SET NOCOUNT ON 使用情况

2022-01-23 00:00:00 sql concurrency tsql sql-server ado.net

灵感来自 this question对SET NOCOUNT有不同看法的地方...

<块引用>

我们应该对 SQL Server 使用 SET NOCOUNT ON 吗?如果没有，为什么不呢?

它的作用编辑 6，2011 年 7 月 22 日

它会在任何 DML 之后抑制​​xx 行受影响"消息.这是一个结果集，在发送时，客户端必须对其进行处理.它很小，但可以测量(请参阅下面的答案)

对于触发器等，客户端将收到多个受影响的 xx 行"，这会导致某些 ORM、MS Access、JPA 等出现各种错误(请参阅下面的编辑)

背景:

普遍接受的最佳实践(直到这个问题我才想到)是在 SQL Server 的触发器和存储过程中使用 SET NOCOUNT ON.我们在任何地方都使用它，快速的谷歌显示很多 SQL Server MVP 也同意.

MSDN 说这会破坏 .net SQLDataAdapter.

现在，这对我来说意味着 SQLDataAdapter 仅限于完全简单的 CRUD 处理，因为它希望匹配n 行受影响"消息.所以，我不能使用:

  • IF EXISTS 以避免重复(没有行影响消息)注意:谨慎使用
  • WHERE NOT EXISTS(少于预期的行数
  • 过滤掉琐碎的更新(例如，实际上没有数据更改)
  • 之前进行任何表访问(例如日志记录)
  • 隐藏复杂性或去规范化
  • 等

在问题中 marc_s(谁知道他的 SQL 知识)说不要使用它.这与我的想法不同(我认为自己在 SQL 方面也有一定的能力).

我可能遗漏了一些东西(请随意指出明显的)，但是你们那里的人怎么看?

注意:我已经好几年没有看到这个错误了，因为我现在不使用 SQLDataAdapter.

评论和问题后的

更多想法...

我们有多个客户端:一个可能使用 C# SQLDataAdaptor，另一个可能使用来自 Java 的 nHibernate.这些可以通过 SET NOCOUNT ON 以不同的方式受到影响.

如果您将存储过程视为方法，那么假设某些内部处理以某种方式为您自己的目的工作是不好的形式(反模式).

编辑 2:触发中断 nHibernate 问题，其中 SET NOCOUNT ON 无法设置

(不，它不是 this 的副本)p>

编辑 3:更多信息，感谢我的 MVP 同事

  • KB 240882,导致 SQL 2000 及更早版本断开连接的问题
  • 性能提升演示

编辑 4:2011 年 5 月 13 日

在未指定时也会破坏 Linq 2 SQL?

编辑 5:2011 年 6 月 14 日

破坏 JPA，使用表变量存储过程:JPA 2.0 是否支持 SQL Server 表变量?

编辑 6:2011 年 8 月 15 日

SSMS编辑行"数据网格需要 SET NOCOUNT ON:使用 GROUP BY 更新触发器

编辑 7:2013 年 3 月 7 日

来自@RemusRusanu 的更多详细信息:
SET NOCOUNT ON 真的会产生这么大的性能差异吗

解决方案

好的，现在我已经完成了我的研究，这是交易:

在 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/everything-ever-wanted-know-set-nocount/

Inspired by this question where there are differing views on SET NOCOUNT...

Should we use SET NOCOUNT ON for SQL Server? If not, why not?

What it does Edit 6, on 22 Jul 2011

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)

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)

Background:

General accepted best practice (I thought until this question) is to use SET NOCOUNT ON in triggers and stored procedures in SQL Server. We use it everywhere and a quick google shows plenty of SQL Server MVPs agreeing too.

MSDN says this can break a .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:

  • 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

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).

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.

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.

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 ON can not be set

(and no, it's not a duplicate of this)

Edit 3: Yet more info, thanks to my MVP colleague

  • KB 240882, issue causing disconnects on SQL 2000 and earlier
  • Demo of performance gain

Edit 4: 13 May 2011

Breaks Linq 2 SQL too when not specified?

Edit 5: 14 Jun 2011

Breaks JPA, stored proc with table variables: Does JPA 2.0 support SQL Server table variables?

Edit 6: 15 Aug 2011

The SSMS "Edit rows" data grid requires SET NOCOUNT ON: Update trigger with GROUP BY

Edit 7: 07 Mar 2013

More in depth details from @RemusRusanu:
Does SET NOCOUNT ON really make that much of a performance difference

解决方案

Ok now I've done my research, here is the deal:

In TDS protocol, SET NOCOUNT ON only saves 9-bytes per query while the text "SET NOCOUNT ON" itself is a whopping 14 bytes. I used to think that 123 row(s) affected was 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_PROC embedded in the response. It's not a separate network packet so no roundtrips are wasted.

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.

Here is a very detailed analysis about insignificance of SET NOCOUNT setting: http://daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount/

相关文章