SET NOCOUNT (Transact-SQL)SET NOCOUNT (Transact-SQL)

适用于: 是SQL Server是Azure SQL 数据库是Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

阻止在结果集中返回显示受 Transact-SQLTransact-SQL 语句或存储过程影响的行计数的消息。Stops the message that shows the count of the number of rows affected by a Transact-SQLTransact-SQL statement or stored procedure from being returned as part of the result set.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
SET NOCOUNT { ON | OFF }   

RemarksRemarks

当 SET NOCOUNT 为 ON 时,不返回计数。When SET NOCOUNT is ON, the count is not returned. 当 SET NOCOUNT 为 OFF 时,返回计数。When SET NOCOUNT is OFF, the count is returned.

即使当 SET NOCOUNT 为 ON 时,也更新 @@ROWCOUNT 函数。The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

当 SET NOCOUNT 为 ON 时,将不向客户端发送存储过程中每个语句的 DONE_IN_PROC 消息。SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. 如果存储过程中包含一些并不返回许多实际数据的语句,或者如果过程包含 Transact-SQLTransact-SQL 循环,网络通信流量便会大量减少,因此,将 SET NOCOUNT 设置为 ON 可显著提高性能。For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQLTransact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

SET NOCOUNT 指定的设置是在执行或运行时生效,而不是在分析时生效。The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.

要查看此设置的当前设置,请运行以下查询。To view the current setting for this setting, run the following query.

DECLARE @NOCOUNT VARCHAR(3) = 'OFF';  
IF ( (512 & @@OPTIONS) = 512 ) SET @NOCOUNT = 'ON';  
SELECT @NOCOUNT AS NOCOUNT;  
  

权限Permissions

要求 公共 角色具有成员身份。Requires membership in the public role.

示例Examples

以下示例将禁止显示受影响的行数的消息。The following example prevents the message about the number of rows affected from being displayed.

USE AdventureWorks2012;  
GO  
SET NOCOUNT OFF;  
GO  
-- Display the count message.  
SELECT TOP(5)LastName  
FROM Person.Person  
WHERE LastName LIKE 'A%';  
GO  
-- SET NOCOUNT to ON to no longer display the count message.  
SET NOCOUNT ON;  
GO  
SELECT TOP(5) LastName  
FROM Person.Person  
WHERE LastName LIKE 'A%';  
GO  
-- Reset SET NOCOUNT to OFF  
SET NOCOUNT OFF;  
GO  

另请参阅See Also

@@ROWCOUNT (Transact-SQL) @@ROWCOUNT (Transact-SQL)
SET 语句 (Transact-SQL)SET Statements (Transact-SQL)