SET NOCOUNT (T-SQL)

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.

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

Permissions

SET NOCOUNT permissions default to all users.

Examples

This example (when executed in the osql utility or SQL Server Query Analyzer) prevents the message (about the number of rows affected) from being displayed.

USE pubs

GO

-- Display the count message.

SELECT au_lname

FROM authors

GO

USE pubs

GO

-- SET NOCOUNT to ON and no longer display the count message.

SET NOCOUNT ON

GO

SELECT au_lname

FROM authors

GO

-- Reset SET NOCOUNT to OFF.

SET NOCOUNT OFF

GO

  

See Also
@@ROWCOUNT SET

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.