Bits of .NET

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;