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. By default, the number of rows affected by a query statement are returned, but this can be turned on and off by using SET NOCOUNT.

Explanation

As mentioned above, there is not really a 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 if neeed to be used in the code, so turning SET NOCOUNT ON will not change that behavior.

SET NOCOUNT Default Behavior

By default SET NOCOUNT is OFF and will return the number of rows affected. Here is an example:

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

We could then run the stored procedure as follows:

EXEC dbo.uspGetAddress 'Calgary'

If we look at the Messages in SSMS we would see something like 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.

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

We could run the stored procedure again as follows:

EXEC dbo.uspGetAddress 'Calgary'

The Messages tab 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 @@ROWCOUNT will still return a value.

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

We could run the stored procedure again as follows:

EXEC dbo.uspGetAddress 'Calgary'

The Messages tab 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" as follows:

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

We could run the stored procedure again as follows:

EXEC dbo.uspGetAddress 'Calgary'

The Messages tab would be similar to this, showing the counts both ways for the rows affected.