Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Reducing amount of network data for SQL Server stored procedures



By:
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:

(23 row(s) affected)

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:

Command(s) completed successfully.

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:

23

SET NOCOUNT OFF

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






More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, October 28, 2016 - 6:39:34 AM - Fatima Fayyaz Back To Top

Its a vry helpul and easy to understand article. Thanks 


Learn more about SQL Server tools