Use of SET NOCOUNT

SET NOCOUNT
Causes the returned results to not contain information about the number of rows affected by Transact-SQL statements.
Grammar
SET NOCOUNT {on | OFF}
Comments
When SET NOCOUNT is on, the count is not returned (representing the number of rows affected by Transact-SQL statements). When SET NOCOUNT is off, the count is returned.

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

When SET NOCOUNT is on, the client is not sent Done_in_proc information for each statement in the stored procedure. When you execute a query using the utility provided by Microsoft®sql Server™, the "NN rows affected" will not appear in the query results when Transact-SQL statements (such as SELECT, INSERT, UPDATE, and DELETE) end. "。

If some of the statements contained in a stored procedure do not return much of the actual data, this setting can significantly improve performance because of a significant reduction in network traffic.

Set NOCOUNT settings are set at execution or Run-time, not at parse time.
Permissions
SET NOCOUNT permissions are granted to all users by default.
Example
The following example, when executed in the osql utility or SQL Server Query Analyzer, prevents the display of information about the number of rows affected.

Use pubsgo--to Display the count message. SELECT au_lname from Authorsgouse pubsgo--SET NOCOUNT to and no longer display the count message. Set NOCOUNT ongoselect au_lname from authorsgo--Reset SET NOCOUNT to OFF. SET NOCOUNT Offgo







Attention:





When SET NOCOUNT is on, the count is not returned (representing the number of rows affected by Transact-SQL statements). When SET NOCOUNT is off, the count is returned.

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

When SET NOCOUNT is on, the client is not sent Done_in_proc information for each statement in the stored procedure. When you execute a query using the utility provided by Microsoft®sql Server™, the "NN rows affected" will not appear in the query results when Transact-SQL statements (such as SELECT, INSERT, UPDATE, and DELETE) end. "。

If some of the statements contained in a stored procedure do not return much of the actual data, this setting can significantly improve performance because of a significant reduction in network traffic.

Set NOCOUNT settings are set at execution or Run-time, not at parse time.








In Query Analyzer, messages such as "How many rows are affected" are not displayed