SQL SET NOCOUNT ON 使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1483732/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SET NOCOUNT ON usage
提问by gbn
Inspired by this questionwhere there are differing views on SET NOCOUNT...
受到这个问题的启发，在那里对 SET NOCOUNT 有不同的看法......
Should we use SET NOCOUNT ON for SQL Server? If not, why not?
我们应该为 SQL Server 使用 SET NOCOUNT ON 吗？如果没有，为什么不呢？
What it doesEdit 6, on 22 Jul 2011
它的作用编辑 6，2011 年 7 月 22 日
It suppresses the "xx rows affected" message after any DML. This is a resultset and when sent, the client must process it. It's tiny, but measurable (see answers below)
它在任何 DML 之后抑制“xx 行受影响”消息。这是一个结果集，发送时，客户端必须对其进行处理。它很小，但可以衡量（见下面的答案）
For triggers etc, the client will receive multiple "xx rows affected" and this causes all manner of errors for some ORMs, MS Access, JPA etc (see edits below)
对于触发器等，客户端将收到多个“受影响的 xx 行”，这会导致某些 ORM、MS Access、JPA 等出现各种错误（请参阅下面的编辑）
Background:
背景：
General accepted best practice (I thought until this question) is to use SET NOCOUNT ONin triggers and stored procedures in SQL Server. We use it everywhere and a quick google shows plenty of SQL Server MVPs agreeing too.
一般公认的最佳实践（我认为直到这个问题）是SET NOCOUNT ON在 SQL Server 中的触发器和存储过程中使用。我们在任何地方都使用它，快速 google 显示大量 SQL Server MVP 也同意。
MSDN says this can break a .net SQLDataAdapter.
MSDN 说这会破坏.net SQLDataAdapter。
Now, this means to me that the SQLDataAdapter is limited to utterly simply CRUD processing because it expects the "n rows affected" message to match. So, I can't use:
现在，这对我来说意味着 SQLDataAdapter 仅限于完全简单的 CRUD 处理，因为它期望“n 行受影响”消息匹配。所以，我不能使用：
- IF EXISTS to avoid duplicates (no rows affected message) Note: use with caution
- WHERE NOT EXISTS (less rows then expected
- Filter out trivial updates (eg no data actually changes)
- Do any table access before (such as logging)
- Hide complexity or denormlisation
- etc
- IF EXISTS 以避免重复（无行影响消息）注意：谨慎使用
- WHERE NOT EXISTS（比预期的行少
- 过滤掉琐碎的更新（例如没有数据实际更改）
- 之前进行任何表访问（例如日志记录）
- 隐藏复杂性或去规范化
- 等等
In the question marc_s (who knows his SQL stuff) says do not use it. This differs to what I think (and I regard myself as somewhat competent at SQL too).
在问题 marc_s （谁知道他的 SQL 东西）说不要使用它。这与我的想法不同（而且我认为自己在 SQL 方面也有一定的能力）。
It's possible I'm missing something (feel free to point out the obvious), but what do you folks out there think?
我可能遗漏了一些东西（随意指出明显的），但是你们那里的人怎么看？
Note: it's been years since I saw this error because I don't use SQLDataAdapter nowadays.
注意：自从我看到这个错误已经好几年了，因为我现在不使用 SQLDataAdapter。
Edits after comments and questions:
评论和问题后的编辑：
Edit: More thoughts...
编辑：更多想法...
We have multiple clients: one may use a C# SQLDataAdaptor, another may use nHibernate from Java. These can be affected in different ways with SET NOCOUNT ON.
我们有多个客户端：一个可能使用 C# SQLDataAdaptor，另一个可能使用 Java 中的 nHibernate。这些可以通过不同的方式受到影响SET NOCOUNT ON。
If you regard stored procs as methods, then it's bad form (anti-pattern) to assume some internal processing works a certain way for your own purposes.
如果您将存储过程视为方法，那么假设某些内部处理以某种方式为您自己的目的工作是不好的形式（反模式）。
Edit 2: a trigger breaking nHibernate question, where SET NOCOUNT ONcan not be set
编辑 2：一个触发打破 nHibernate 问题，哪里SET NOCOUNT ON不能设置
(and no, it's not a duplicate of this)
（不，它不是一个重复的这个）
Edit 3: Yet more info, thanks to my MVP colleague
编辑 3：还有更多信息，感谢我的 MVP 同事
- KB 240882, issue causing disconnects on SQL 2000 and earlier
- Demo of performance gain
Edit 4: 13 May 2011
编辑 4：2011 年 5 月 13 日
Breaks Linq 2 SQL too when not specified?
Edit 5: 14 Jun 2011
编辑 5：2011 年 6 月 14 日
Breaks JPA, stored proc with table variables: Does JPA 2.0 support SQL Server table variables?
使用表变量破坏 JPA、存储过程：JPA 2.0 是否支持 SQL Server 表变量？
Edit 6: 15 Aug 2011
编辑 6：2011 年 8 月 15 日
The SSMS "Edit rows" data grid requires SET NOCOUNT ON: Update trigger with GROUP BY
SSMS“编辑行”数据网格需要 SET NOCOUNT ON：使用 GROUP BY 更新触发器
Edit 7: 07 Mar 2013
编辑 7：2013 年 3 月 7 日
More in depth details from @RemusRusanu:
Does SET NOCOUNT ON really make that much of a performance difference
来自@RemusRusanu 的更深入的细节：
SET NOCOUNT ON 真的能带来如此大的性能差异吗？
采纳答案by Sedat Kapanoglu
Ok now I've done my research, here is the deal:
好的，现在我已经完成了我的研究，这是交易：
In TDS protocol, SET NOCOUNT ONonly saves 9-bytes per querywhile the text "SET NOCOUNT ON" itself is a whopping 14 bytes. I used to think that 123 row(s) affectedwas returned from server in plain text in a separate network packet but that's not the case. It's in fact a small structure called DONE_IN_PROCembedded in the response. It's not a separate network packet so no roundtrips are wasted.
在 TDS 协议中，每个查询SET NOCOUNT ON仅节省9 个字节，而文本“SET NOCOUNT ON”本身则高达 14 个字节。我曾经认为这123 row(s) affected是在单独的网络数据包中以纯文本形式从服务器返回的，但事实并非如此。它实际上是一个称为DONE_IN_PROC嵌入在响应中的小结构。它不是一个单独的网络数据包，因此不会浪费往返。
I think you can stick to default counting behavior almost always without worrying about the performance. There are some cases though, where calculating the number of rows beforehand would impact the performance, such as a forward-only cursor. In that case NOCOUNT might be a necessity. Other than that, there is absolutely no need to follow "use NOCOUNT wherever possible" motto.
我认为您几乎可以始终坚持默认计数行为，而不必担心性能。但在某些情况下，预先计算行数会影响性能，例如只进游标。在这种情况下，NOCOUNT 可能是必需的。除此之外，绝对没有必要遵循“尽可能使用 NOCOUNT”的座右铭。
Here is a very detailed analysis about insignificance of SET NOCOUNTsetting: http://daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount/
这里有一个非常详细的关于SET NOCOUNT设置无意义的分析：http: //daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount/
回答by StriplingWarrior
It took me a lot of digging to find real benchmark figures around NOCOUNT, so I figured I'd share a quick summary.
我花了很多时间才找到关于 NOCOUNT 的真实基准数据，所以我想我会分享一个快速总结。
- If your stored procedure uses a cursor to perform a lot of very quick operations with no returned results, having NOCOUNT OFF can take roughly 10 times as long as having it ON. 1This is the worst-case scenario.
- If your stored procedure only performs a single quick operation with no returned results, setting NOCOUNT ON mightyield around a 3% performance boost. 2This would be consistent with a typical insert or update procedure. (See the comments on this answer for some discussion about why this may not always be faster.)
- If your stored procedure returns results (i.e. you SELECT something), the performance difference will diminish proportionately with the size of the result set.
回答by Bhaumik Patel
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. It is used with any SELECT, INSERT, UPDATE, DELETE statement.
The setting of SET NOCOUNT is set at execute or run time and not at parse time.
SET NOCOUNT ON improves stored procedure (SP) performance.
Syntax: SET NOCOUNT { ON | OFF }
当 SET NOCOUNT 为 ON 时，不返回计数（指示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。它与任何 SELECT、INSERT、UPDATE、DELETE 语句一起使用。
SET NOCOUNT 的设置是在执行或运行时设置的，而不是在解析时设置的。
SET NOCOUNT ON 提高了存储过程 (SP) 的性能。
语法：SET NOCOUNT { ON | 离开 }
Example of SET NOCOUNT ON:
SET NOCOUNT ON 示例：
Example of SET NOCOUNT OFF:
SET NOCOUNT OFF 示例：
回答by marc_s
I guess to some degree it's a DBA vs. developer issue.
我想在某种程度上这是 DBA 与开发人员的问题。
As a dev mostly, I'd say don't use it unless you absolutely positively have to - because using it can break your ADO.NET code (as documented by Microsoft).
作为一名开发人员，我会说除非您绝对必须使用它，否则不要使用它 - 因为使用它会破坏您的 ADO.NET 代码（如 Microsoft 所述）。
And I guess as a DBA, you'd be more on the other side - use it whenever possible unless you really must prevent it's usage.
而且我想作为 DBA，你会更多地站在另一边 - 尽可能使用它，除非你真的必须阻止它的使用。
Also, if your devs ever use the "RecordsAffected" being returned by ADO.NET's ExecuteNonQuerymethod call, you're in trouble if everyone uses SET NOCOUNT ONsince in this case, ExecuteNonQuery will always return 0.
此外，如果您的开发人员曾经使用 ADO.NET 的ExecuteNonQuery方法调用返回的“RecordsAffected” ，那么如果每个人都使用，您就会遇到麻烦，SET NOCOUNT ON因为在这种情况下，ExecuteNonQuery 将始终返回 0。
Also see Peter Bromberg's blog postand check out his position.
另请参阅 Peter Bromberg 的博客文章并查看他的立场。
So it really boils down to who gets to set the standards :-)
所以这真的归结为谁来制定标准:-)
Marc
马克
回答by Chris J
If you're saying you might have different clients as well, there are problems with classic ADO if SET NOCOUNT is not set ON.
如果您说您也可能有不同的客户端，那么如果 SET NOCOUNT 未设置为 ON，经典 ADO 就会出现问题。
One I experience regularly: if a stored procedure executes a number of statements (and thus a number of "xxx rows affected" messages are returned), ADO seems not to handle this and throws the error "Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."
我经常遇到的一个：如果一个存储过程执行了许多语句（因此返回了许多“受影响的 xxx 行”消息），ADO 似乎不处理这个并抛出错误“无法更改 Recordset 对象的 ActiveConnection 属性它有一个 Command 对象作为它的来源。”
So I generally advocate setting it ON unless there's a really reallygood reason not to. you may have found the really really good reason which I need to go and read into more.
所以我通常主张将其设置为 ON ，除非有非常好的理由不这样做。您可能已经找到了我需要阅读更多内容的非常好的理由。
回答by Tao
At the risk of making things more complicated, I encourage a slightly different rule to all those I see above:
冒着让事情变得更复杂的风险，我鼓励采用与我上面看到的所有规则略有不同的规则：
- Always set
NOCOUNT ONat the top of a proc, before you do any work in the proc, but alsoalwaysSET NOCOUNT OFFagain, before returning any recordsets from the stored proc.
NOCOUNT ON在 proc 中执行任何工作之前，始终设置在 proc 的顶部，但也始终SET NOCOUNT OFF在从存储的 proc 返回任何记录集之前再次设置。
So "generally keep nocount on, except when you are actually returning a resultset". I don't know any ways that this can break any client code, it means client code never needs to know anything about the proc internals, and it isn't particularly onerous.
因此，“通常不要指望，除非您实际返回结果集”。我不知道这会破坏任何客户端代码的任何方式，这意味着客户端代码永远不需要了解有关 proc 内部的任何信息，而且它并不是特别繁重。
回答by zvolkov
Regarding the triggers breaking NHibernate, I had that experience first-hand. Basically, when NH does an UPDATE it expects certain number of rows affected. By adding SET NOCOUNT ON to the triggers you get the number of rows back to what NH expected thereby fixing the issue. So yeah, I would definitely recommend turning it off for triggers if you use NH.
关于破坏 NHibernate 的触发器，我有亲身体验。基本上，当 NH 执行 UPDATE 时，它预计会受到一定数量的行影响。通过将 SET NOCOUNT ON 添加到触发器，您可以将行数恢复到 NH 预期的值，从而解决问题。所以是的，如果您使用 NH，我绝对建议您将其关闭以触发触发器。
Regarding the usage in SPs, it's a matter of personal preference. I had always turned the row count off, but then again, there are no real strong arguments either way.
关于在 SP 中的使用，这是个人喜好的问题。我总是关闭行计数，但话说回来，无论哪种方式都没有真正有力的论据。
On a different note, you should really consider moving away from SP-based architecture, then you won't even have this question.
另一方面，您真的应该考虑摆脱基于 SP 的架构，然后您甚至不会有这个问题。
回答by phil_w
I wanted to verify myself that 'SET NOCOUNT ON' does not save a network packet nor a roundtrip
我想验证自己“SET NOCOUNT ON”不会保存网络数据包或往返
I used a test SQLServer 2017 on another host (I used a 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
Then I traced packets on port 1433 with the tool 'Wireshark':
'capture filter' button -> 'port 1433'
我在另一台主机上使用了 SQLServer 2017 测试（我使用了虚拟机）
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
exec procNoCount
this is the response packet:
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
这是响应包：
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
exec procNormal
this is the response packet:
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
这是响应包：
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
On line 40 I can see '07' which is the number of 'row(s) affected'. It is included in the response packet. No extra packet.
在第 40 行，我可以看到“07”，它是“受影响的行数”。它包含在响应数据包中。没有额外的包。
It has however 13 extra bytes which could be saved, but probably not more worth it than reducing column names (e.g. 'ManagingDepartment' to 'MD')
然而，它有 13 个可以保存的额外字节，但可能并不比减少列名更值得（例如，'ManagingDepartment' 到 'MD'）
So I see no reason to use it for performance
所以我认为没有理由使用它来提高性能
BUT As others mentioned it can break ADO.NET and I also stumbled on an issue using python: MSSQL2008 - Pyodbc - Previous SQL was not a query
但是正如其他人提到的，它可以破坏 ADO.NET，我还偶然发现了一个使用 python 的问题： MSSQL2008 - Pyodbc - 以前的 SQL 不是查询
So probably a good habit still...
所以可能仍然是一个好习惯......
回答by bcoughlan
One place that SET NOCOUNT ONcan really help is where you are doing queries in a loop or a cursor. This can add up to a lot of network traffic.
一个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
Turning on client statistics in SSMS, a run of EXEC NoCountOnand EXEC NoCountOffshows that there was an extra 390KB traffic on the NoCountOff one:
在 SSMS 中打开客户端统计，运行EXEC NoCountOn并EXEC NoCountOff显示 NoCountOff 上有额外的 390KB 流量：
Probably not ideal to be doing queries in a loop or cursor, but we don't live in in ideal world either :)
在循环或游标中进行查询可能并不理想，但我们也不生活在理想世界中 :)
回答by Subhransu Panda
SET NOCOUNT ON; Above code will stop the message generated by sql server engine to fronted result window after the DML/DDL command execution.
设置无计数；上面的代码将在 DML/DDL 命令执行后停止由 sql server 引擎生成的消息到前端结果窗口。
Why we do it? As SQL server engine takes some resource to get the status and generate the message, it is considered as overload to the Sql server engine.So we set the noncount message on.
我们为什么要这样做？由于SQL server 引擎需要一些资源来获取状态并生成消息，这被认为是Sql server 引擎的过载。所以我们设置了非计数消息。
