When you use SET NOCOUNT ON, the message that indicates the number of rows that are affected by the T-SQL statement is not returned as part of the results. When you use SET NOCOUNT OFF; the count is returned. Using SET NOCOUNT ON can improve performance because network traffic can be reduced. SET NOCOUNT ON prevents SQL Server from sending DONE_IN_PROC message for each statement in a stored procedure or batch of SQL statements.
For example, if we have some 4 operations in the Stored Procedure, four messages are returned to the caller. Each message contains the number of rows affected by the respective statement. When you use SET NOCOUNT ON. you reduce the processing that SQL Server performs and the size of the response that is sent across the network.
Note: In Query Analyzer, the DONE_IN_PROC message is intercepted and displayed as "N rows affected".
