SET NOCOUNT ON to improve Stored Procedure Performance in SQL Server

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.

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.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.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

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.

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.

CREATE PROCEDURE uspGetAddress @City nvarchar(30)

AS

SET NOCOUNT ON

SELECT * 

FROM AdventureWorks.Person.Address

WHERE City = @City

PRINT @@ROWCOUNT

GO

The messages that are returned would be similar to this:

23

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

Related Posts

1. Good to use CLOB instead of VARCHAR2 in Java Stored Procedure
2. Oracle PL/SQL: Advanced Cursors
3. Transactions in SQL- Server 2008
4. Consistency Errors in MS SQL Server 2000
5. Ad hoc Reporting using SQL Server Reporting Services 2005

Tags: @@RowCount, NOCOUNT, Performance Tuning Tips, SET NOCOUNT OFF, SET NOCOUNT ON, SQL Server 2008, T-SQL Row Count