If it is ON , SQL Server stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results

Syntax is : SET NOCOUNT { ON | OFF }

It is normally used in Stored Procedures. It prevents message indicated the no or rows affected.This can provide a significant performance boost because network traffic is greatly reduced.

When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

The setting of SET NOCOUNT is set at execute or run time and not at parse time.

SET NOCOUNT permissions default to all users. No extra permissions are required to SET NOCOUNT ON.

Pawan Kumar