NOCOUNT is by default deactivated (OFF) at the server level. It means by default, the server will send to the client the number of rows affected by the SQL query executed which is, in most cases, useless because no one will read this information.
Deactivating this feature will save some network traffic and improve the execution performance of stored procedures and triggers that’s why it is recommended to define SET NOCOUNT ON at the beginning of the definition of PROCEDUREs and TRIGGERs before any query is processed.
This rule raises an issue when NOCOUNT is not set or is set to OFF between the beginning of the PROCEDURE (or TRIGGER) definition and the first statement that is not a SET, IF, or DECLARE.
Good Example – 1:
CREATE PROCEDURE dbo.MyProc(@debug INT) AS BEGIN DECLARE @var INT; IF @debug = 0 BEGIN SET NOCOUNT ON; END SELECT COUNT(*) FROM MY_TABLE END;
Good Example – 2:
CREATE TRIGGER MyTrigger ON MyTable AFTER INSERT AS BEGIN SET NOCOUNT ON; [...] END;