No SET NOCOUNT ON before DML

It is recommended to set NOCOUNT to ON.

It is a good idea to add a set NOCOUNT to ON statement before any DML statements in a batch to prevent the count of the result of a SQL expression being sent to the users application like a print statement in the message queue rather than as a result. The value can be gotten using the @@ROWCOUNT function.

Available in

 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.

It is recommended to add a SET NOCOUNT ON at the start of every stored procedure, trigger and dynamically executed batch. This rule will apply to all triggers, without exception. No stored procedures will need these messages either, unless they are called from outside the database by an application that is trying to use them to get a gauge of the number of rows of a result. In general, when design9ing an application, it is better to send the count in an output variable, using the value in @@RowCount.

Read more on the Redgate Hub