Whenever use SQL Server Management Studio and create a new stored procedure, it starts by filling the body of the procedure in with the following line:
SET NOCOUNT ON
Could someone explain to me what that does, when I should be leaving it in my stored procedure, and when I should be removing it?
MSDN tells us that NOCOUNT:
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.
It also tells us when its useful to use it:
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.
So basically it can aid performance when you have a sProc that executes multiple statements, but that don't return a great deal of data.
A while back, some ODBC libraries got entirely confused by DONE_IN_PROC messages, so it was actually needed in order to make client code function correctly. I don't believe that is the case any more with current generation libraries.
SSRS (Report Services) likes you to use them in your Stored Procedures that support a report. Apparently too many messages coming back from the stored procedure can confuse the report processing.
