14 December 2010

SET NOCOUNT { ON | OFF } in DOT NET

SET NOCOUNT { ON | OFF } in DOT NET

Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned
as part of the results.
Syntax

SET NOCOUNT { ON | OFF }

Remarks

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.

SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a
stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries,
the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such
as SELECT, INSERT, UPDATE, and DELETE.

For stored procedures that contain several statements that do not return much actual data, this can
provide a significant performance boost because network traffic is greatly reduced.

No comments:

Post a Comment

Comments Welcome