SET NOCOUNT ON Improves Stored Procedure Performance in SQL Server

Problem
One of the biggest things that DBAs try to do on a daily basis is to ensure that their database systems run as fast as possible. As more and more users access the databases and the databases continue to grow, performance slow downs are almost inevitable. Based on this, DBAs and developers should do everything they possibly can to keep performance related issues in mind early in the database lifecycle. This is not always easy to do, because of the unknowns and the changes that occur over time, but there are some simple things that can be done and we will touch upon one of these in this tip.

Solution
Sometimes even the simplest things can make a difference. One of these simple items that should be part of every stored procedure is SET NOCOUNT ON. This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

Here is the old template style available in SQL Server 2000 without the SET NOCOUNT ON.

-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

CREATE PROCEDURE <procedure_name, sysname, proc_test>
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
SELECT @p1, @p2
GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <value_for_param2, , 2>
GO

Here is the new template style available in SQL Server 2005 with the SET NOCOUNT ON.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Write PIVOT queries in SQL Server 2005

Takeaway: SQL Server 2005′s PIVOT operator allows you to rotate row level data into tabular data without the use of the CASE statement. Tim Chapman describes why this new operator has a place in the TSQL developers’ toolbelt.

New to SQL Server 2005 is the PIVOT operator, which gives you the ability to rotate row level data into tabular data without the use of the CASE statement, as was necessary in previous versions of SQL Server.

CASE statement queries

Transforming data from row-level data to columnar data is nothing new to database developers. In previous versions of SQL Server, a series of CASE statements were used along with a series of aggregation queries to arrive at the required cross-tab data set. While this approach gives the developer a high degree of control over the data returned, the resulting queries are often cumbersome to write.

In order to take a look at how these CASE statements are used, I’ll need some test data. Run the scripts below to create the SalesHistory table and load data into the table.

CREATE TABLE SalesHistory
(
      SaleID INT IDENTITY(1,1),
      Product VARCHAR(30),
      SaleDate SMALLDATETIME,
      SalePrice MONEY
)

DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=100)
BEGIN
     INSERT INTO SalesHistory
     (Product, SaleDate, SalePrice)
     VALUES
     ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57) )

     INSERT INTO SalesHistory
     (Product, SaleDate, SalePrice)
     VALUES
     ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13) )

     INSERT INTO SalesHistory
     (Product, SaleDate, SalePrice)
     VALUES
     ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29) )

     SET @i = @i + 1
END

Now I can construct our CASE statement-based PIVOT query. The goal of the query is to aggregate the sales by year for each product available: the BigScreen, PoolTable, and Computer. Each product will have its sales data available in a dedicated column. Below script contains the CASE statement query.

SELECT
      CAST(YEAR(SaleDate) AS VARCHAR(4)) AS SaleDate,
      SUM(CASE WHEN Product = 'BigScreen' THEN SalePrice END) AS BigScreen,
      SUM(CASE WHEN Product = 'PoolTable' THEN SalePrice END) AS PoolTable,
      SUM(CASE WHEN Product = 'Computer' THEN SalePrice END) AS Computer
      FROM
       SalesHistory

GROUP BY
       CAST(YEAR(SaleDate) AS VARCHAR(4))

This query, while straightfoward and relatively easy to write, is cumbersome. You must explicitly type a CASE statement for each row you want to transform to tabular data, which is not much fun. It would be great if a construct would do this for you automatically.  this is where the PIVOT operator comes into play.

PIVOT

With the new PIVOT operator in SQL Server 2005, the CASE statement and GROUP BY statements are no longer necessary. (Every PIVOT query involves an aggregation of some type, so you can omit the GROUP BY statement.) The PIVOT operator provides the same functionality that we tried to achieve with the CASE statement query, but you can achieve it through less code, and it is a bit more pleasing on your eyes. Below script is a sample PIVOT query to mimic our CASE statement query.

SELECT
      CAST(YEAR(SaleDate) AS VARCHAR(4)), BigScreen, PoolTable, Computer
FROM
      SalesHistory
PIVOT
(
      SUM(SalePrice) FOR Product IN(BigScreen, PoolTable, Computer)
) AS p

The meat of this query is contained in the parentheses after the PIVOT operator. Inside the parentheses, we are using a SUM aggregate function to sum up the SalePrice for each Product listed in the FOR Product IN() statement. This statement may seem somewhat odd because the values in the IN() statement are not enclosed in tick (‘) marks to signify that they are literal values. These values are actually treated as ColumnNames, and they are transformed into tabular fields in the final result set.

If you run this query, you will get a result similar to the one from our CASE statement query. The big difference is that this query still returns one row for each row listed in our table, which is not desirable; we want a distinct list of rows for each year of sales listed in our table. The problem lies in the way we first constructed our PIVOT query.

In the SELECT statement in the query, we simply listed the year and a field for each product listed in our SalesHistory table. The problem is that the SELECT statement that precedes the PIVOT clauses cannot specify which columns to use in the PIVOT clause. The PIVOT clause applies the grouping to all columns that are not the pivoted or aggregated fields. We can use a subquery to achieve the desired results.  This small change allows us to mimic our CASE statement query with less code.

SELECT
      SaleDate, BigScreen, PoolTable, Computer
FROM
(
      SELECT CAST(YEAR(SaleDate) AS VARCHAR(4)) AS SaleDate, SalePrice, Product
      FROM SalesHistory
) AS rf
PIVOT
(
         SUM(SalePrice) FOR Product IN(BigScreen, PoolTable, Computer)
) AS p

Limitations

The PIVOT operator is useful in many situations, yet there are drawbacks. Much of the functionality provided by the PIVOT operator requires you to “hard code” the fields that you are pivoting into columnar data. You can overcome this by building the PIVOT query through dynamic TSQL statements, but this is not the most desirable solution.

Another potential drawback of using the PIVOT clause is that you can only aggregrate one field in the result set; in the CASE statement queries, you can aggregate as many fields as needed.

While these potential setbacks may seem like a big deterrent from using the new PIVOT operator, it still has its place in the TSQL developers’ toolbelt.