SET NOCOUNT (TSQL Statement)

stops sending to the client the messages showing the number of rows affected.

Syntax

SET NOCOUNT { ON | OFF }

Remarks

When this option is OFF, the @@ROWCOUNT value is sent to the client, showing the number of rows affected by the statements INSERT, UPDATE, DELETE, BULK INSERT and BULK EXPORT.

All other statements ignore this setting. In particular, the statement SELECT always returns the number of rows affected. Indeed, it must notify the client that all records have been sent and this notification contains the record count.

When this option is ON, the message notifying the number of rows affected is not sent.

This option is always OFF at the beginning of a batch.

If a script contains many batches, this option doesn’t cross the batch boundaries.

This setting is in effect during execution of the query, not during the parse time.

Examples

This query inserts 50000 records into the table mytable.

Because SET NOCOUNT is set to ON, 50000 messages ‘# row(s) affected’ are not sent to the client, which speeds up the query.

CREATE TABLE mytable (a INT IDENTITY NOT NULL, b VARCHAR(50) NULL);
GO

SET NOCOUNT ON;

DECLARE @i INT = 0;

BEGIN TRAN;

WHILE @i<50000
    BEGIN
    INSERT mytable VALUES ('Row_' + CAST(@i AS VARCHAR));
    SET @i += 1;
    END

COMMIT;