UPDATE 后@@ROWCOUNT 是否可靠地衡量*匹配* 行? [英] Is @@ROWCOUNT after UPDATE reliably a measure of *matching* rows?

问题描述

@@ROWCOUNT 是否可靠地告诉您有多少行匹配 UPDATE 中的 WHERE 子句，如相对于它实际改变的地方有多少?

Does @@ROWCOUNT reliably tell you how many rows matched the WHERE clause in an UPDATE, as opposed to how many where actually changed by it?

在 @@ROWCOUNT 的文档中 它说:

In the documentation for @@ROWCOUNT it says:

数据操作语言 (DML) 语句将 @@ROWCOUNT 值设置为查询影响的行数，并将该值返回给客户端.

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client.

(我的重点.)

但是如果我有

CREATE TABLE [Foo] ([a] INT, [b] INT)
GO
INSERT INTO [Foo] ([a], [b]) VALUES (1, 1),(1, 2),(1, 3),(2, 2)
GO
UPDATE [Foo] SET [b] = 1 WHERE [a] = 1
SELECT @@ROWCOUNT
GO

...我看到 3(匹配 [a] = 1 的行数)，而不是 2(行数由 UPDATE 修改 - 三行之一已经具有 b 的值 1).这似乎是对受影响"的奇怪定义.(不是错误，只是与我通常使用这个词的方式不一致——事实上，它实际上对我想做的事情非常方便).

...I see 3 (the number of rows matching [a] = 1), not 2 (the number of rows modified by the UPDATE — one of the three rows already had the value 1 for b). This seems like an odd definition of "affected" (not wrong, just at odds with how I'd normally use the word — it's actually quite handy for what I want to do, in fact).

(类似 MySQL 例如，在这种情况下，ROW_COUNT 函数将返回 2.)

(The similar MySQL ROW_COUNT function, for instance, would return 2 in this situation.)

这种可靠的行为是否理想地记录在我还没有找到的地方?或者是否有奇怪的边缘情况...

Is this reliable behavior, ideally documented somewhere I just haven't found? Or are there odd edge cases...

要明确:我不是在问3是否是正确的答案.我在问这是否是一个可靠的答案，或者是否存在 SQL Server 会遗漏匹配但不需要更改的行的边缘情况.

To be clear: I'm not asking if 3 is the right answer. I'm asking if it's a reliable answer, or are there edge cases where SQL Server will leave out rows that matched but didn't require a change.

更新:一些人询问(或暗示)什么样的可靠性"?我担心的问题.事实是它们非常模糊，但是，我不知道，复制?交易?分区?它可以用来避免查找行的索引，因为它知道 b 已经是 1，所以它会跳过那些?...?

Update: A couple of people have asked (or hinted at) what kind of "reliability" issues I'm worried about. The fact is they're quite nebulous, but, I don't know, replication? Transactions? Partitioning? Indexes it could use to avoid seeking to rows because it knows that b is already 1, and so it skips those? ...?

更新:我希望有一个更内幕"的人查看 SQL Server 如何工作来回答这个问题，但它看起来像触发器示例(以及我玩过的其他示例)作者:xacinay 与我们将要得到的一样接近.它看起来非常坚固；如果它在正常情况下表现得那样，尽管分区或什么都没有，正如有人说的那样，那肯定会成为一个错误.这只是经验性的而不是学术性的.

Update: I was hoping for someone with a more "insider" view of how SQL Server works to answer this question, but it looks like the triggers example (and others I've played with) by xacinay is as close as we're going to get. And it seems pretty darned solid; if it behaves that way in the normal case and it didn't behave that way despite partitioning or whatsit, as someone said, surely that would qualify as a bug. It's just empirical rather than academic.

推荐答案

文档因为 @@ROWCOUNT 告诉你真相，因为 3 行会可靠受到影响，而不是 MySQL 的 ROW_COUNT().

不是 2(由 UPDATE 修改的行数——三个行已经为 b 设置了值 1).

not 2 (the number of rows modified by the UPDATE — one of the three rows already had the value 1 for b).

对于UPDATE，新值和以前的值是否相同并不重要.它只是按照其指示执行操作:查找数据源，根据提供的条件过滤行，并将设置"更改应用于过滤的行.

For UPDATE it's not important if the new and previous values are identical. It simply does what its told to: finds data source, filters rows according to provided condition, and applies 'set' changes to filtered rows.

这就是 SQL Server 毫无保留的工作方式.MySQL 的工作方式可能有所不同.行计数过程不是 SQL 标准的一部分.因此，每次从一个 RDBMS 切换到另一个 RDBMS 时，您都必须在寻找这些人工制品之前先看看.

查看实际更新行为的一些触发器:

Some triggers to see actual update behaviour:

CREATE TRIGGER [dbo].[trgFooForUpd]
ON [dbo].[Foo]
FOR UPDATE 
AS begin declare @id int;
      select @id = [a] from INSERTED;
      select * from INSERTED; end;
GO
CREATE TRIGGER [dbo].[trgFooAfterUpd]
ON [dbo].[Foo]
AFTER UPDATE 
AS print 'update done for ' + cast(coalesce( @@ROWCOUNT, -1) as varchar )+'rows'

这篇关于UPDATE 后@@ROWCOUNT 是否可靠地衡量*匹配* 行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！