Reducing amount of network data for SQL Server stored procedures

By: Greg Robidoux

Overview

There are many tricks that can be used when you write T-SQL code.  One of these is to reduce the amount of network data for each statement that occurs within your stored procedures.  Every time a SQL statement is executed it returns the number of rows that were affected.  By using "SET NOCOUNT ON" within your stored procedure you can shut off these messages and reduce some of the traffic.

Explanation

As mentioned above there is not really any reason to return messages about what is occuring within SQL Server when you run a stored procedure.  If you are running things from a query window, this may be useful, but most end users that run stored procedures through an application would never see these messages. 

You can still use @@ROWCOUNT to get the number of rows impacted by a SQL statement, so turning SET NOCOUNT ON will not change that behavior.

Not using SET NOCOUNT ON

Here is an example without using SET NOCOUNT ON:

-- not using SET NOCOUNT ON 
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM Person.Address
WHERE City = @City
GO

The messages that are returned would be similar to this:

Using SET NOCOUNT ON

This example uses the SET NOCOUNT ON as shown below.  It is a good practice to put this at the beginning of the stored procedure.

-- using SET NOCOUNT ON 
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SET NOCOUNT ON
SELECT * 
FROM Person.Address
WHERE City = @City
GO

The messages that are returned would be similar to this:

Using SET NOCOUNT ON and @@ROWCOUNT

This example uses SET NOCOUNT ON, but will still return the number of rows impacted by the previous statement.  This just shows that this still works.

-- not using SET NOCOUNT ON 
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SET NOCOUNT ON
SELECT * 
FROM Person.Address
WHERE City = @City
PRINT @@ROWCOUNT
GO

The messages that are returned would be similar to this:

SET NOCOUNT OFF

If you wanted to turn this behavior off, you would just use the command "SET NOCOUNT OFF".