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 beha…

Accepted Answer

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'
```

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

Solution

Problem