Set Nocount On

Colonel Angus · April 21st, 2004, 08:48 AM

I know that SQL Server counts the rows it returns, but if I'm not going to need the row count, am I taking a performance hit by allowing this default behavior?

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.

pgtips · April 21st, 2004, 09:54 AM

yes you are taking a performance hit with the default behaviour. Quote from Microsoft docs:

Quote:

quote:SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with MicrosoftÂ® SQL Serverâ¢ to execute queries, the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

For stored procedures that contain several statements that do not return much actual data, this can provide a significant performance boost because network traffic is greatly reduced.

hth
Phil

Colonel Angus · April 21st, 2004, 10:21 AM

Thanks!

- - - - - - - - - - - - - - - - - - - - - - -
In God we trust, everything else we test.