Is @@ROWCOUNT after UPDATE reliably a measure of *matching* rows?

Question

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?

In the documentation for @@ROWCOUNT it says:

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.

(My emphasis.)

But if I have

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 

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

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

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.

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

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.

Answer 1

The documentation for @@ROWCOUNT is telling you the truth because 3 rows would be reliably affected as opposed to MySQL's ROW_COUNT().

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

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.

That's the way SQL Server works without any reservations. MySQL may work different. A row counting procedure is not a part of the SQL standard. So, you have to look before you leap for those kinds of artefacts every time you switch from one RDBMS to another.

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' 

Answer 2

To expand on xacinay's answer because he is correct.

You have 3 rows changed and therefore @@ROWCOUNT is accurate. The SQL Server changes all rows, it does not verify that a value is in fact different before changing it, as that would require a lot of overhead on update commands. Just imagining having to check a VARCHAR(MAX) for whether the value was actually changed or not.

The easiest way to illustrate this is to actually change yor UPDATE query to something like this:

UPDATE [Foo]  SET [b] = 1 OUTPUT INSERTED.b WHERE [a] = 1 

It will output 3 rows of INSERTED which is the 'pseudo' table that holds the new values for a given update/insert command. That the value in fact is already b = 1 in one instance does not matter.

If you want that to matter you'll need to include it in your WHERE clause:

UPDATE [Foo]  SET [b] = 1 WHERE [a] = 1 AND [b] <> 1 SELECT @@ROWCOUNT 

Alternatively, and as a more general way of doing this check, you can make a trigger and compare the values/fields in the DELETED table with the values in the INSERTED table and use that as foundation for whether a row is actually 'changed'.

So - 3 is the accurate number as you have updated 3 rows because 3 rows were touched by [a] = 1