受此问题的启发，在SET NOCOUNT ...上存在不同的观点...


我们是否应将SET NOCOUNT ON用于SQL Server？如果不是，为什么呢？


它的功能编辑6，2011年7月22日

它抑制了任何DML之后的“受影响的xx行”消息。这是一个结果集，发送时，客户端必须对其进行处理。它很小，但是可以测量（请参见下面的答案）

对于触发器等，客户端将收到多个“受影响的xx行”，这会导致某些ORM，MS Access，JPA等各种错误（请参见以下内容进行编辑）

背景：

一般公认的最佳实践（我想直到这个问题）是在SQL Server的触发器和存储过程中使用SET NOCOUNT ON。我们到处使用它，一个快速的google也显示出很多SQL Server MVP也同意。

MSDN表示这会破坏.net SQLDataAdapter。

现在，这对我来说意味着SQLDataAdapter仅限于完全CRUD处理，因为它希望“ n个受影响的行”消息匹配。因此，我不能使用：


如果存在则避免重复（不影响行的消息）注意：谨慎使用

不存在（行少）然后预期的是
过滤掉琐碎的更新（例如，实际上没有数据更改）
在进行任何表访问之前（例如记录）
隐藏复杂性或去甲化处理
等

/>在问题marc_s（谁知道他的SQL知识）说不要使用它。这与我的想法有所不同（我也认为自己在SQL方面也很称职）。

我可能我遗漏了一些东西（可以随意指出明显的地方），但是您在那里的人怎么想呢？

注意：距今我已经看到这个错误已有好几年了，因为我现在不使用SQLDataAdapter。

在评论和问题之后进行编辑：

编辑：更多想法...

我们有多个客户端：一个可以使用C＃SQLDataAdaptor，另一个可以使用Java的nHibernate。使用SET NOCOUNT ON可以用不同的方式影响这些。

如果将存储的procs作为方法，那么以某种方式为自己的目的假定某些内部处理工作是很不好的形式（反模式）。 br />
编辑2：触发了一个打破nHibernate问题的触发器，其中无法设置SET NOCOUNT ON

（不，这不是它的重复）

编辑3：更多信息，多亏了我的MVP同事



KB 240882，导致在SQL 2000和更早版本上断开连接的问题

性能提升示例

编辑4：2011年5月13日

未指定时也会破坏Linq 2 SQL吗？

编辑5：2011年6月14日

打破JPA，使用表变量存储proc：JPA 2.0是否支持SQL Server表变量？

编辑6：2011年8月15日

SSMS“编辑行”数据网格需要设置NOCOUNT ON：使用GROUP BY更新触发器

编辑7：2013年3月7日

来自@RemusRusanu的更多详细信息：是否确实设置了NOCOUNT ON发挥很大的性能差异

评论

@AlexKuznetsov：什么是“线程安全”方法？当然，在EXISTS中执行的读取仍将包括任何未完成的交易吗？
@Jeremy Seghi：抱歉回复晚。 （#rows受影响的）消息是由SSMS等解释的客户端工具内容：但是有一个包含此信息的数据包。当然，我知道@@ rowcount的工作原理等，但这不是问题的重点...
别担心。我个人同意您的观点。我只是在评论说，IF / WHERE EXISTS构造的结果和SET NOCOUNT之间没有直接关联。无论NOCOUNT如何，我都能从这些构造中获得一致的结果。如果您有其他疑问，请发送给我。
@Jeremy Seghi：您是正确的：SET NOCOUNT ON仅抑制多余的数据包返回给客户端。如果@@ ROWCOUNT等不受影响。哦，它破坏了SQLDataAdapters ... :-)
@基恩·约翰斯通：事后看来，这是一个措辞不佳的问题。如果不是我的问题，我会投票决定关闭。

#1 楼

好的，现在我已经完成了研究，这就是问题所在：

在TDS协议中，SET NOCOUNT ON每个查询仅保存9个字节，而文本“ SET NOCOUNT ON”本身却高达14个字节。我曾经以为123 row(s) affected是从服务器以纯文本格式在单独的网络数据包中返回的，但事实并非如此。实际上，响应中嵌入了一个名为DONE_IN_PROC的小结构。它不是一个单独的网络数据包，因此不会浪费往返时间。

我认为您几乎可以始终坚持默认计数行为，而不必担心性能。但是，在某些情况下，事先计算行数会影响性能，例如仅向前的游标。在这种情况下，可能需要NOCOUNT。除此之外，绝对没有必要遵循“尽可能使用NOCOUNT”的座右铭。

以下是有关SET NOCOUNT设置无关紧要的非常详细的分析：http://daleburnett.com/2014/01 /一切都想知道没有设置/

评论

确实。我一直在永久使用SET NOCOUNT ON，但是marc_s在另一个问题中指出了SQLDataAdapter的局限性。

– gbn
09-10-10在12:26
谢谢。字节或大小对我来说不是问题，但客户端必须对其进行处理。仍然令我惊讶的是SQLDataAdapter依赖关系...

– gbn
09-10-11在6:38
感谢您的回答。由于您的调查，我会接受这一点，这引发了我的更多信息和工作。不过，我在管理费用上存在分歧：其他答案表明这很重要。干杯，gbn

– gbn
09-10-16在15:43
它不是通过电缆的往返延迟的字节数，这是性能的杀手

–竞速蜗牛
13年7月9日在20:20
在TDS消息流中，一个例子是仅将值插入表中。 DONINPROC（RPC）或DONE（BATCH）消息将流计数设置为受影响的行，而done_count标志为true时，无论NO_COUNT是否为ON。当查询保留执行select的SELECT语句或RPC调用时，取决于客户端lib的实现，它可能需要禁用计数...当禁用时，仍对select语句计数行，但标志DONE_COUNT设置为false。务必阅读您的客户端库的建议，因为它将解释令牌（消息）流而不是您

–米兰·贾里克（Milan Jaric）
19年7月31日在10:34

#2 楼

我花了很多时间才能找到围绕NOCOUNT的真实基准数据，因此我想分享一个简短的摘要。


如果您的存储过程使用游标执行了很多操作，非常快速的操作，没有返回结果，关闭NOCOUNT可能需要大约10倍的时间。 1这是最坏的情况。
如果存储过程仅执行单个快速操作而没有返回结果，则将NOCOUNT设置为ON可能会提高3％的性能。 2这将与典型的插入或更新过程一致。 （请参阅此答案的注释，以获取有关为什么这样做可能不总是更快的一些讨论。）
如果您的存储过程返回结果（即，您选择SELECT），则性能差异将与结果集的大小成比例地减小。


评论

+1对光标的影响，这与我的观察一致

–zvolkov
2011年5月2日15:52
第二点不准确！我的意思是它所指的博客。从来不是！无论NO_COUNT设置为ON还是OFF，都以相同的大小发送DONE和DONEPROC和DONEINPROC。由于ULONGLONG（64字节）仍然存在RowCount，并且DONE_COUNT标志仍然存在，但是位值为0。SQLServer仍将对行数进行计数，即使您不希望从DONE令牌中读取值也是如此。如果您阅读@@ ROWCOUNT，则您将更多字节以返回值令牌的形式或作为另一个colmetadata +行令牌的形式添加到令牌流中！

–米兰·贾里克（Milan Jaric）
19年8月1日在10:02
@MilanJaric：感谢您提出来。您帮助我意识到我链接了错误的文章。现在，该链接已更新，并且本文提出了引人注目的论点，以表明使用SET NOCOUNT ON可以稍微改善性能。您认为所使用的基准测试方法存在问题吗？

–StriplingWarrior
19年8月1日在17:09
:)关于SET NOCOUNT OFF / ON仍然不准确，错误是第二个SP没有SET NOCOUNT OFF；这就是为什么他们认为自己没有得到额外的字节作为响应的原因。准确的基准测试是在左侧存储过程中使用SET NOCOUNT ON，在左侧使用SET NOCOUNT OFF。这样，您将获得带有DONEINPROC（设置NOCOUNT ...），十个再次DONEINPROC（INSERT语句），然后返回RETURNVALUE（@@ ROWCOUNT）的TDS程序包，然后返回sp的RETURNSTATUS0和最终的DONPROC。因为第二个sp的主体没有SET NOCOUNT OFF，所以存在错误！

–米兰·贾里克（Milan Jaric）
19年8月2日在16:18
改写他们发现的内容，但他们没有意识到的是，如果您有1K个提取游标的请求，请首先发出一个将NOCOUNT设置为ON或OFF进行连接的请求，然后使用相同的连接调用游标提取1K次以节省一些带宽。 NOCOUNT ON或OFF的实际连接状态不会影响带宽，可能会混淆客户端库，例如ADO.net或ODBC。因此，“如果您关心带宽，请不要使用SET NOCOUNT ” ：）

–米兰·贾里克（Milan Jaric）
19年8月2日在16:22


#3 楼


当SET NOCOUNT为ON时，不返回计数（指示受Transact-SQL语句影响的行数）。当SET NOCOUNT为OFF时，返回计数。它与任何SELECT，INSERT，UPDATE，DELETE语句一起使用。
SET NOCOUNT的设置在执行或运行时设置，而不是在解析时设置。
SET NOCOUNT ON改善存储过程（SP）的性能。 。
语法：SET NOCOUNT {ON | OFF}

SET NOCOUNT ON的示例：



SET NOCOUNT OFF的示例：



评论

通过屏幕快照轻松快速地理解。很好。 :)

– Shaiju T
18年8月15日在14:07

#4 楼

我猜在某种程度上，这是DBA与开发人员的问题。

作为一名开发人员，我想说除非您绝对肯定要使用它，否则不要使用它-因为使用它会破坏您的ADO.NET代码（如Microsoft所述）。

作为DBA，我想您会处于另一面-除非确实必须防止使用它，否则请尽可能使用它。

另外，如果您的开发人员曾经使用ADO.NET的ExecuteNonQuery方法调用返回的“ RecordsAffected”，如果每个人都使用SET NOCOUNT ON，您会遇到麻烦，因为在这种情况下，ExecuteNonQuery始终返回0。

另请参阅Peter Bromberg的博客文章并查看他的位置。

所以真正归结于谁来设定标准：-)

Marc

评论

不过，他只讲简单的CRUD：他提到的数据网格可以使用xml发送多行以避免往返等。

– gbn
09-09-27 15:00
我想如果您从不使用SqlDataAdapters，并且从不检查并依赖ExecuteNonQuery返回的“受影响的记录”数字（例如，如果您使用类似Linq-to-SQL或NHibernate的东西），那么您可能没有任何问题在所有存储的过程中使用SET NOCOUNT ON。

– marc_s
09-9-27 at 15:09

#5 楼

如果您说自己也可能有其他客户端，那么如果未将SET NOCOUNT设置为ON，则经典ADO会出现问题。

我经常遇到的一个问题：如果存储过程执行许多语句（因此返回了许多“受影响的xxx行”消息），则ADO似乎无法处理此问题并抛出错误“无法更改具有Command对象作为源的Recordset对象的ActiveConnection属性。“

因此，除非确实有充分的理由，否则我通常主张将其设置为ON。您可能已经找到了我需要去阅读的更多真正理由。

#6 楼

冒着使事情变得更加复杂的风险，我鼓励采取与上述规则稍有不同的规则：


在进行任何工作之前，始终将proc43设置为proc的顶部。从存储的proc返回任何记录集之前，还要再次进行proc。

因此，“除非您实际上正在返回结果集，否则通常不要计数”。我不知道这会破坏任何客户端代码的任何方式，这意味着客户端代码永远不需要了解proc内部的任何信息，并且它也不是特别繁琐。

评论

谢谢。您当然可以从DataSet或使用容器中获取行数，但可能很有用。我们无法在SELECT上使用触发器，因此这是安全的：大多数客户端错误是由数据更改中的虚假消息引起的。

– gbn
2011年7月22日11:07


该规则的问题在于，要比“是否在proc的顶部设置NOCOUNT ON吗？”更难测试。我想知道像SQL Enlight这样的SQL Analysis工具是否可以测试这种事情……将其添加到我的SQL格式化程序项目的长期待办事项列表中：）

–陶
11年7月22日在11:20

#7 楼

关于破坏NHibernate的触发器，我有第一手的经验。基本上，当NH执行UPDATE时，它期望受影响的行数一定。通过将SET NOCOUNT ON添加到触发器，您可以将行数恢复到NH期望值，从而解决了该问题。是的，如果您使用NH，我绝对建议您将其关闭以用于触发器。

关于SP中的用法，这是个人喜好问题。我总是关闭行计数，但是再一次，两种方式都没有真正的强力论据。

换句话说，您应该真正考虑摆脱基于SP的体系结构，然后您会赢甚至没有这个问题。

评论

我不同意离开存储过程。这意味着我们必须在2个不同的客户端代码库中使用相同的SQL，并信任我们的客户端编码器。我们是开发人员DBA。而且，您的意思不是“ SET NOCOUNT ON”吗？

– gbn
09-10-10在12:57
@CodeBlend：仅使用Google即可获得比您需要的更多的东西。但是... stackoverflow.com/a/4040466/27535

– gbn
2013年1月7日14:27

#8 楼

我想证明自己“ SET NOCOUNT ON”既不保存网络数据包也不进行往返操作

我在另一台主机上使用了测试SQLServer 2017（我使用了VM）
create table ttable1 (n int); insert into ttable1 values (1),(2),(3),(4),(5),(6),(7) go
/> create procedure procNoCount as begin set nocount on update ttable1 set n=10-n end create procedure procNormal as begin update ttable1 set n=10-n end
然后我用工具'Wireshark'在端口1433上跟踪了数据包：
'捕获过滤器'按钮->'端口1433'

exec procNoCount

这是响应数据包：
0000 00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010 00 42 d0 ce 40 00 40 06 84 0d c0 a8 32 88 c0 a8 0020 32 01 05 99 fe a5 91 49 e5 9c be fb 85 01 50 18 0030 02 b4 e6 0e 00 00 04 01 00 1a 00 35 01 00 79 00 0040 00 00 00 fe 00 00 e0 00 00 00 00 00 00 00 00 00

exec procNormal

这是响应数据包：
0000 00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010 00 4f d0 ea 40 00 40 06 83 e4 c0 a8 32 88 c0 a8 0020 32 01 05 99 fe a5 91 49 e8 b1 be fb 8a 35 50 18 0030 03 02 e6 1b 00 00 04 01 00 27 00 35 01 00 ff 11 0040 00 c5 00 07 00 00 00 00 00 00 00 79 00 00 00 00 0050 fe 00 00 e0 00 00 00 00 00 00 00 00 00

>在第40行上，我可以看到“ 07”，它是“受影响的行”的数量。
它包含在响应数据包中。没有额外的数据包。

它可以节省13个额外的字节，但可能比减少列名（例如'ManagingDepartment'到'MD'）更值得。

因此，我认为没有理由将其用于性能

但
正如其他人提到的那样，它可能会破坏ADO.NET
，我也偶然发现了使用python的问题：
MSSQL2008-Pyodbc-以前的SQL并非查询

所以可能还是个好习惯...

#9 楼

SET NOCOUNT ON;


这行代码在SQL中用于不返回在执行查询时受影响的行数。如果我们不需要受影响的行数，则可以使用它，因为这将有助于节省内存使用并增加查询执行的速度。

评论

请注意，@@ ROWCOUNT仍被设置。 SET NOCOUNT ON禁止SQL Server发送到客户端的任何额外响应。请参阅上面接受的答案

– gbn
2012年5月17日下午6:51

#10 楼

SET NOCOUNT ON;
以上代码将在执行DML / DDL命令后将sql服务器引擎生成的消息停止到前端结果窗口。

为什么要这样做？
作为SQL服务器引擎需要一些资源来获取状态并生成消息，这被认为是Sql服务器引擎的重载。因此，我们将noncount消息设置为on。

#11 楼

SET NOCOUNT ON真正可以提供帮助的地方是您在循环或游标中进行查询的位置。这可能会增加很多网络流量。

CREATE PROCEDURE NoCountOn
AS
set nocount on
    DECLARE @num INT = 10000
    while @num > 0
    begin
       update MyTable SET SomeColumn=SomeColumn
       set @num = @num - 1
    end
GO


CREATE PROCEDURE NoCountOff
AS
set nocount off
    DECLARE @num INT = 10000
    while @num > 0
    begin
       update MyTable SET SomeColumn=SomeColumn
       set @num = @num - 1
    end
GO


在SSMS中打开客户端统计信息，运行EXEC NoCountOn和EXEC NoCountOff表示，存在额外的390KB流量NoCountOff之一：



在循环或游标中执行查询可能不理想，但我们也不生活在理想的世界中：）

#12 楼

我知道这是一个很老的问题。

使用“ SET NOCOUNT ON”的最佳方法是将其设置为SP中的第一条语句，然后在最后一条SELECT语句之前再次将其设置为OFF。

#13 楼

SET NOCOUNT ON甚至允许访问受影响的行，例如：

SET NOCOUNT ON

DECLARE @test TABLE (ID int)

INSERT INTO @test
VALUES (1),(2),(3)

DECLARE @affectedRows int = -99  

DELETE top (1)
  FROM @test
SET @affectedRows = @@rowcount

SELECT @affectedRows as affectedRows


结果



受影响的行

1


消息


命令成功完成。

完成时间：2020-06-18T16 ：20：16.9686874 + 02：00


#14 楼


SET NOCOUNT ON-将显示“命令已成功完成”。
SET NOCOUNT OFF-将显示“（（受影响的行数））”。


#15 楼

我不知道如何在客户端和SQL之间测试SET NOCOUNT ON，所以我测试了其他SET命令“ SET TRANSACTION ISOLATION LEVEL READ MITIMMITTED”的类似行为。

我从连接更改发送了一条命令SQL的默认行为（读取已提交），下一个命令已更改它。
当我在存储过程中更改ISOLATION级别时，它并没有更改下一个命令的连接行为。

当前结论，


更改存储过程中的设置不会更改连接默认设置。
通过使用ADOCOnnection发送命令来更改设置会更改默认行为。

我认为这与其他SET命令有关，例如“ SET NOCOUNT ON”

评论

您上面的第1点是否表示您真的不需要在末尾设置NOCOUNT OFF，因为它不会影响全局环境？

–funkymushroom
2014年3月21日15:25
我不确定这是否是他对第1点的意思，但是在我的测试中，是的，显然全局环境不受存储过程中的SET NOCOUNT ON的影响。

–道格
16年1月28日在19:35


这是一个比较差的选择，因为“隔离级别”与特定交易明确相关，因此没有特殊理由期望它与诸如NOCOUNT之类的设置一致

–IMSoP
3月23日9:54

#16 楼

如果（设置无计数==关闭）

{
，那么它将保留受影响的记录数的数据
从而降低性能
}
else
{
它不会跟踪更改记录
因此改善了性能
}
}

#17 楼

有时，即使最简单的事情也可以有所作为。 SET NOCOUNT ON应该是每个存储过程的一部分，这些简单的项目之一。放在存储过程顶部的这一行代码关闭了执行每个T-SQL语句后SQL Server发送回客户端的消息。对所有SELECT，INSERT，UPDATE和DELETE语句执行此操作。在查询窗口中运行T-SQL语句时，拥有此信息很方便，但是在运行存储过程时，则无需将该信息传递回客户端。

通过删除此信息网络带来的额外开销可以极大地提高数据库和应用程序的整体性能。

如果仍然需要获取受正在执行的T-SQL语句影响的行数，则仍然可以使用@@ROWCOUNT选项。通过发出SET NOCOUNT ON，此函数（@@ROWCOUNT）仍然有效，并且仍可以在您的存储过程中使用，以标识该语句影响了多少行。