What is the ‘SET NOCOUNT ON|OFF’ clause ??

25 Oct

Usually, when you create a new store procedure you will see a default clause added automatically by SQL Server in the beginning of your store procedures as following:



Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

In other words, this can reduce network traffic, as your client will not receive the message indicating the number of rows affected by a T-SQL statement.

In other words too, 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.

Leave a comment

Posted by on October 25, 2011 in MS SQL Server


Tags: , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: