nocount on_在SQL Server中设置NOCOUNT ON语句的用法和性能优势

最新推荐文章于 2023-07-28 11:54:01 发布

culuo4781

最新推荐文章于 2023-07-28 11:54:01 发布

阅读量1.5k

点赞数 3

文章标签：触发器数据库 python mysql java

原文链接：https://www.sqlshack.com/set-nocount-on-statement-usage-and-performance-benefits-in-sql-server/

版权

nocount on

Have you ever noticed SET NOCOUNT ON statement in T-SQL statements or stored procedures in SQL Server? I have seen developers not using this set statement due to not knowing it.

您是否曾经在T-SQL语句或SQL Server中的存储过程中注意到SET NOCOUNT ON语句？我已经看到开发人员由于不知道而未使用此set语句。

In this article, we will explore why it is a good practice to use SET NOCOUNT ON with T-SQL statements. We will also learn the performance benefit you can get from it.

在本文中，我们将探讨为什么将SET NOCOUNT ON与T-SQL语句一起使用是一种好习惯。我们还将学习您可以从中获得的性能优势。

介绍 (Introduction)

Before we explore this statement, let’s create a sample table in the SQL Server database with the following script.

在探索此语句之前，让我们使用以下脚本在SQL Server数据库中创建一个示例表。

USE SQLShackDemo;
  GO
  CREATE TABLE tblEmployeeDemo
  (Id      INT
   PRIMARY KEY, 
   EmpName NVARCHAR(50), 
   Gender  NVARCHAR(10),
  );

Let’s insert a few records data in this table using the following script.

让我们使用以下脚本在此表中插入一些记录数据。

USE [SQLShackDemo]
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (1, N'Grace', N'Female')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (2, N'Gordon', N'Male')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (3, N'Jaime', N'Female')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (4, N'Ruben', N'Female')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (5, N'Makayla', N'Male')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (6, N'Barry', N'Female')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (7, N'Ramon', N'Male')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (8, N'Douglas', N'Male')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (9, N'Julian', N'Female')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (10, N'Sarah', N'Female')
  GO

Once you execute the command, you get the following message in SSMS. You get ‘1-row affected’ message for each row.

执行命令后，您将在SSMS中收到以下消息。您会在每一行收到“ 1行受影响”消息。

Suppose you insert a million rows in the table and for each record, you get this message. As you can see, it is not a useful message and does not provide relevant information.

假设您在表中插入一百万行，并且对于每条记录，都会收到此消息。如您所见，它不是有用的消息，并且不提供相关信息。

Now, let’s select the records from this table.

现在，让我们从该表中选择记录。

SELECT *
  FROM tblEmployeeDemo;

In the output, we get two tabs Results and Messages.

在输出中，我们得到两个选项卡结果和消息。

Result tab shows the record from the table
结果选项卡显示表中的记录
The messages tab gives the number of rows affected message
消息选项卡提供了受影响消息的行数

Let’s create a stored procedure to get the records from the specified table.

让我们创建一个存储过程，以从指定的表中获取记录。

CREATE PROCEDURE SP_tblEmployeeDemo
  AS
      BEGIN
          SELECT *
          FROM tblEmployeeDemo;
      END;

Execute this stored procedure, and you get a similar output in the following screenshot as well.

执行此存储过程，在以下屏幕截图中也将获得类似的输出。

Exec SP_tblEmployeeDemo

Output of a stored procedure in the results and message tab

SET NOCOUNT ON/OFF statement controls the behavior in SQL Server to show the number of affected rows in the T-SQL query.

SET NOCOUNT ON / OFF语句控制SQL Server中的行为，以显示T-SQL查询中受影响的行数。

SET NOCOUNT OFF – By default, SQL Server shows the number of affected rows in the messages pane SET NOCOUNT OFF –默认情况下，SQL Server在消息窗格中显示受影响的行数
SET NOCOUNT ON – We can specify this set statement at the beginning of the statement. Once we enable it, we do not get the number of affected rows in the output SET NOCOUNT ON –我们可以在语句的开头指定此set语句。一旦启用它，就不会在输出中获得受影响的行数

Let’s try running the previous queries with NOCOUNT statement.

让我们尝试使用NOCOUNT语句运行先前的查询。

Execute the insert statement after enabling the NOCOUNT

启用NOCOUNT后执行插入语句

TRUNCATE TABLE tblEmployeeDemo;
  GO
  SET NOCOUNT ON
  USE [SQLShackDemo]
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (1, N'Grace', N'Female')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (2, N'Gordon', N'Male')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (3, N'Jaime', N'Female')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (4, N'Ruben', N'Female')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (5, N'Makayla', N'Male')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (6, N'Barry', N'Female')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (7, N'Ramon', N'Male')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (8, N'Douglas', N'Male')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (9, N'Julian', N'Female')
  GO
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (10, N'Sarah', N'Female')
  GO

Once we execute the above queries we do not get messages of 1 row(s) affected. It gives the following message.

一旦执行上述查询，我们将不会受到1行消息的影响。它给出以下消息。

Execute the Select statement with NOCOUNT ON, and you get the following output

使用NOCOUNT ON执行Select语句，您将获得以下输出
```
SET NOCOUNT ON
  SELECT *
  FROM tblEmployeeDemo;
```
Similar to the insert statement, we did not get the number of rows affected message in the select statement output.

与insert语句类似，在select语句输出中没有得到受影响的行数。
Execute Stored procedure

执行存储过程

We cannot directly execute the stored procedure with the SET NOCOUNT ON statement. The above statement does not work on the stored procedure.

我们无法使用SET NOCOUNT ON语句直接执行存储过程。上面的语句不适用于存储过程。
```
SET NOCOUNT ON
  	Exec SP_tblEmployeeDemo
```
We either need to create a new stored procedure or alter the procedure and add the SET NOCOUNT statement as per the following script.

我们要么需要创建一个新的存储过程，要么更改该过程并按照以下脚本添加SET NOCOUNT语句。
```
ALTER PROCEDURE SP_tblEmployeeDemo
  AS
      BEGIN
          SET NOCOUNT ON;
          SELECT *
          FROM tblEmployeeDemo;
      END;
```
Execute the stored procedure, and we get the required output without the number of affected rows message.

执行存储过程，我们将获得所需的输出，而没有受影响的行数消息。
```
Exec SP_tblEmployeeDemo
```

在实例级别配置NOCOUNT的行为 (Configure the behavior of NOCOUNT at instance level)

The SET NOCOUNT ON works at the session-level. We need to specify it with each session. In the stored procedures, we specify the code itself. Therefore, it does not require specifying explicitly in the session.

SET NOCOUNT ON在会话级别起作用。我们需要在每个会话中指定它。在存储过程中，我们指定代码本身。因此，它不需要在会话中明确指定。

We can use the sp_configure configuration option to use it at the instance level. The following query sets the behavior of SET NOCOUNT ON at the instance level.

我们可以使用sp_configure配置选项在实例级别使用它。以下查询在实例级别设置SET NOCOUNT ON的行为。

EXEC sys.sp_configure 'user options', '512'; 
  RECONFIGURE

If we specify the NOCOUNT ON/OFF in the individual session, we can override the behavior configured at the instance level.

如果我们在各个会话中指定NOCOUNT ON / OFF，则可以覆盖在实例级别配置的行为。

SET NOCOUNT和@@ ROWCOUNT函数 (SET NOCOUNT and @@ROWCOUNT function)

We can use the @@ROWCOUNT function to get the number of affected rows in SQL Server. The NOCOUNT ON function does not have any impact on the @@ROWCOUNT function.

我们可以使用@@ ROWCOUNT函数来获取SQL Server中受影响的行数。 NOCOUNT ON函数对@@ ROWCOUNT函数没有任何影响。

Execute the following query, and we get the number of rows affected with the Insert statement.

执行以下查询，我们得到Insert语句影响的行数。

TRUNCATE TABLE tblEmployeeDemo;
    GO
    USE [SQLShackDemo]
    GO
    SET NOCOUNT ON
    INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (1, N'Grace', N'Female'),(2, N'Gordon', N'Male')
    Select @@ROWCOUNT  as rowsaffected

SET NOCOUNT ON和SQL触发器 (SET NOCOUNT ON and the SQL Trigger)

Let’s check the impact of the NOCOUNT statement on the SQL Triggers.

让我们检查一下NOCOUNT语句对SQL触发器的影响。

The following command inserts two records in the tblEmployeeDemo table.

以下命令在tblEmployeeDemo表中插入两个记录。

TRUNCATE TABLE tblEmployeeDemo;
  GO
  USE [SQLShackDemo]
  GO
  SET NOCOUNT ON
  INSERT [dbo].[tblEmployeeDemo] ([Id], [EmpName], [Gender]) VALUES (1, N'Grace', N'Female'),(2, N'Gordon', N'Male')
  Select @@ROWCOUNT  as rowsaffected

Create another table to store the record transaction records inserted using the trigger.

创建另一个表来存储使用触发器插入的记录事务记录。

CREATE TABLE Audit_tblEmployee
  (UpdatedID       INT, 
   UpdatedValue    NVARCHAR(10), 
   Audit_Timestamp DATETIME default getdate()
  );

Let’s create a SQL INSERT, Update trigger to capture the records for the insert, update values.

让我们创建一个SQL INSERT，Update触发器来捕获插入，更新值的记录。

CREATE TRIGGER TR_tblEmployeeDemo ON dbo.tblEmployeeDemo
  AFTER INSERT, UPDATE
  AS
       BEGIN
           INSERT INTO Audit_tblEmployee
           (UpdatedID, 
            UpdatedValue
           )
                  SELECT i.ID, 
                         i.Gender
                  FROM inserted AS i;
       END;

Execute the following query to update an existing value in the tblEmployeeDemo table, and it invokes the SQL trigger for inserting record in the Audit_tblEmployee table.

执行以下查询以更新tblEmployeeDemo表中的现有值，并调用SQL触发器以在Audit_tblEmployee表中插入记录。

Update tblEmployeeDemo set Gender='Male' where ID=1

The update statement updates only one record; however, in the following SSMS message, it shows two rows affected.

更新语句仅更新一条记录；但是，在下面的SSMS消息中，它显示了受影响的两行。

It might create issues for us if the further code depends upon the number of rows affected message. We get this message due to update record tblEmployeeDemo and insert record in the Audit_tblEmployee table.

如果进一步的代码取决于受影响的消息的行数，则可能为我们带来问题。由于更新记录tblEmployeeDemo并在Audit_tblEmployee表中插入记录，因此我们收到此消息。

We do not want the result ‘ 1 row affected’ for the data insertion in the audit table. We should use the trigger with SET NOCOUNT ON for suppressing this message.

我们不希望在审计表中插入数据的结果“影响1行”。我们应该将触发器与SET NOCOUNT ON一起使用以禁止显示此消息。

Let’s alter the trigger with the following script:

让我们使用以下脚本更改触发器：

alter TRIGGER TR_tblEmployeeDemo ON dbo.tblEmployeeDemo
  AFTER INSERT, UPDATE
  AS
       BEGIN
  	 SET NOCOUNT ON
           INSERT INTO Audit_tblEmployee
           (UpdatedID, 
            UpdatedValue
           )
                  SELECT i.ID, 
                         i.Gender
                  FROM inserted AS i;
       END;

Rerun the update statement, and we get the expected result and get only the number of affected rows using the update statement.

重新运行update语句，我们将获得预期的结果，并使用update语句仅获得受影响的行数。

SET NOCOUNT impact on the trigger output

NOCOUNT语句对性能的影响 (The Performance impact of NOCOUNT statement)

According to Microsoft documentation, using NOCOUNT option can provide a significant performance improvement.

根据Microsoft文档，使用NOCOUNT选项可以显着提高性能。

Let’s explore this performance benefit with the following example.

让我们通过以下示例探索这种性能优势。

Create two different stored procedures with different NOCOUNT properties.

创建两个具有不同NOCOUNT属性的不同存储过程。

Create a stored procedure with default behavior ( NOCOUNT OFF)

创建具有默认行为的存储过程（NOCOUNT OFF）

CREATE PROC NOCountTest(@N INT)
  AS
       DECLARE @NumberofRecords INT;
       SET @NumberofRecords = 0;
       WHILE @NumberofRecords < @N
           BEGIN
               SET @NumberofRecords = @NumberofRecords + 1;
           END;
      GO

Create stored procedure with explicit set statement

使用显式set语句创建存储过程

CREATE PROC NOCountTest_ON(@N INT)
  AS
  	SET NOCOUNT ON
       DECLARE @NumberofRecords INT;
       SET @NumberofRecords = 0;
       WHILE @NumberofRecords < @N
           BEGIN
              			 SET @NumberofRecords = @NumberofRecords + 1;
           END;
  		 SELECT @NumberofRecords
      GO

Execute both the stored procedures with the different number of rows 1000, 10000, 100000 and 1000000. We want to capture client statistics for these executions. In the query window of SSMS, go to Edit and enable the Include Client Statistics.

使用不同的行数1000、10000、100000和1000000执行这两个存储过程。我们要捕获这些执行的客户端统计信息。在SSMS的查询窗口中，转到编辑并启用包括客户端统计信息 。

Let’s compare the client statistics

让我们比较一下客户统计数据

with SET NOCOUNT OFF

关闭SET NOCOUNT
With SET NOCOUNT ON

开启SET NOCOUNT

Let’s put both screenshots of client statistics together to see a difference

让我们将两个客户端统计信息的屏幕快照放在一起，看看有什么不同

We can see a huge difference in the TDS packagers received from the server, Bytes received from the server and the client processing time. The number of the select statement also shows a significant improvement. We did not specify the Select statement in the stored procedure, but still, SQL Server treats SET statement as a select statement with the default NOCOUNT value. We can reduce the network bandwidth with the SET NOCOUNT ON option in the stored procedures or T-SQL statements. It might not improve the query performance drastically, but definitely, it puts an impact on the processing time, reducing the network bandwidth and client processing times.

我们可以看到，从服务器收到的TDS打包程序，从服务器收到的字节数和客户端处理时间之间存在巨大差异。 select语句的数量也显示出明显的改进。我们没有在存储过程中指定Select语句，但是SQL Server仍将SET语句视为具有默认NOCOUNT值的select语句。我们可以使用存储过程或T-SQL语句中的SET NOCOUNT ON选项减少网络带宽。它可能不会显着提高查询性能，但是可以肯定的是，它会影响处理时间，从而减少网络带宽和客户端处理时间。

结论 (Conclusion)

In this article, we explored the behavior of T-SQL statements and stored procedures using the SET NOCOUNT ON. We should consider this SET option and eliminate unnecessary messages to reduce network traffic and improve performance.

在本文中，我们使用SET NOCOUNT ON探索了T-SQL语句和存储过程的行为。我们应该考虑使用此SET选项，并消除不必要的消息以减少网络流量并提高性能。

翻译自: https://www.sqlshack.com/set-nocount-on-statement-usage-and-performance-benefits-in-sql-server/

nocount on

culuo4781

关注

3
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
nocount on_在SQL Server中设置NOCOUNT ON语句的用法和性能优势

nocount on Have you ever noticed SET NOCOUNT ON statement in T-SQL statements or stored procedures in SQL Server? I have seen developers not using this set statement due to not knowing it. 您是否曾...
复制链接

扫一扫