即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。当 SET NOCOUNT 为 ON 时,将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft® SQL Server™ 提供的实用工具执行查询时,在 Transact-SQL 语句(如 SELECT、INSERT、UPDATE 和 DELETE)结束时将不会在查询结果中显示"nn rows affected"。如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。
Transact-SQL Reference SET NOCOUNT 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 } Res 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 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@@ROWCOUNTSET
SET NOCOUNT
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 } Res
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 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@@ROWCOUNTSET