SET NOCOUNT OFF is used

It is not recommended to set NOCOUNT to OFF.

This is the default setting but it isn’t such a good idea in a production system. The count of the result of a SQL expression is useful but it is actually sent to the application in the message queue rather than as a result. A better way to get the row count value is using the @@ROWCOUNT function.

Available in

 SQL Monitor

SQL Prompt

 SQL Code Guard

Whenever you execute a query, a small (nine-byte) message packet, called DONE_IN_PROC, is sent back to the client. For each T-SQL statement executed, the message displays the number of rows that are affected by that statement. Use of SET NOCOUNT ON suppresses the sending of this message packet. This can improve performance by reducing network traffic slightly. The server-based logic, and values such as @@ROWCOUNT, are all unaffected.

Triggers should not send rowcount messages, so use of SET NOCOUNT OFF for triggers is a code smell. Other code modules, such as stored procedures, should generally avoid sending the messages too (i.e. should generally use SET NOCOUNT ON), unless one or more of the applications using the stored procedures require it to be OFF, because they are reading the row count value from the message.

For further details on this issue see this Product Learning article on the Redgate hub