What is the significance of using “SET NOCOUNT ON” on stored procedures in Sql Server

Every time a SQL statement is executed it returns the number of rows that were affected. And this can lead to two problems:

· As more than necessary data is sent over a network, if the network was already saturated, SQL Server overall performance would suffer.

· If the execution ratio is high, the additional data stored in the client machine memory will affect its performance.

By using “SET NOCOUNT ON” within your stored procedure you can shut off these messages and reduce some of the traffic.

This eliminates the printed message of (xx rows(s) affected) in the Query Analyzer window. It also eliminates a message of DONE_IN_PROC that is communicated from SQL Server to the client application, which causes another round trip across the network.

Let’s take a look at a stored procedure with “SET NOCOUNT ON”:

ALTER PROCEDURE usp_GetUser
AS
SET NOCOUNT ON
SELECT TOP 10 ID,Title,Firstname,LastName FROM [user]
SELECT @@ROWCOUNT AS RowsReturned 
SET NOCOUNT OFF

EXEC usp_GetUser

here is the result we get:

Set Nocounts

To know the effect of “SET NOCOUNT ON” on @@ROWCOUNT , you can have a quick look at this: http://blog.sqlauthority.com/2011/12/02/sql-server-effect-of-set-no-count-on-rowcount/

reference : http://blog.sqlexpert.pl/2013/03/16/the-meaning-of-stored-procedures-part-6-nocount-rowcount-and-rowcount/


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.