SET NOCOUNT ON – A simple Performance Booster

How many of us using the SET NOCOUNT ON statement in our stored procedures? This simple one line code do a lot of differences in performance (in terms of Network Traffic which is one the performance related issue) of SQL Server.

What will this do is simply cut off the number of rows affected by the stored procedures’ Select / Insert / Delete/ Update statements. According to BOOKS ONLINE, When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.

By default value of SET NOCOUNT is OFF.

If your application really wants the number of rows affected, then you can use @@RowCount variable which is very handy.

On reading this BOL, a question is raised on my mind, that what happened if I set this on, to SQLDATAREADER’s RecordsAffected Property ? Is there any link between this SET NCOUNT? But it is not having any link. Because the remark section of the SqlDataReader.::.RecordsAffected Property in MSDN (http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.recordsaffected.aspx) is saying that The RecordsAffected property is not set until all rows are read and you close the SqlDataReader.

So to reduce network traffic, you can add this simple line at the starting of every stored procedure. For further digging read the followings.

http://www.sqlmag.com/Article/ArticleID/22093/sql_server_22093.html

http://weblogs.asp.net/jgalloway/archive/2006/08/30/How-NOCOUNT-affects-ADO.NET.aspx

http://www.mssqltips.com/tip.asp?tip=1226