Is @@ ROWCOUNT after UPDATE a reliable measure of * matching * strings?

Question

Reliability @@ROWCOUNT shows how many rows matched the WHERE in UPDATE , as opposed to how many of them actually changed?

The documentation for @@ROWCOUNT says:

Data Manipulation Language (DML) statements set @@ROWCOUNT to the number of rows affected and return this value to the client.

(My emphasis.)

But if I

 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 lines matching [a] = 1 ), and not 2 (the number of lines changed by UPDATE - one of the three lines already had a value of 1 for b ). This seems like a strange definition of “affected” (not so, it just contradicts the way I usually use the word - in fact it is very convenient for what I want to do).

(A similar MySQL ROW_COUNT function, for example, would return 2 in this situation.)

Is this a robust behavior, ideally documented somewhere that I just didn't find? Or there are cases with odd edges ...

To be clear: I am not asking if 3 correct. I ask if this answers a reliable answer, or if there are extreme cases where SQL Server leaves rows that match but don't require changes.

Update . Several people asked (or hinted) what problems with "reliability" I worry. The fact is that they are quite foggy, but, it is no coincidence, replication? Deals? Markup? Indexes that he can use to avoid searching for strings, since he knows that b already 1 , and so he skips them? ...

Update . I was hoping someone with a more “insider” view of how SQL Server works to answer this question, but it looks like an example of the triggers (and others that I played with) from xacinay as close as we are going to receive. And that seems pretty damn. if he behaves in this way in the normal case, and he did not behave this way, despite the separation or whatsit, as someone said, surely this would qualify as a mistake. It is simply empirical, not academic.

Answer 1

The documentation for @@ROWCOUNT tells you the truth, because 3 rows will be reliably affected, not MySQL ROW_COUNT () .

not 2 (the number of rows changed by UPDATE - one of the three rows already had a value of 1 for b).

For UPDATE doesn't matter if the new and previous values ​​match. It simply does what it says: it finds the data source, filters the rows according to the provided condition, and applies the “set” changes to the filtered rows.

The way SQL Server works without any reservations. MySQL may work differently. String counting is not part of the SQL standard. Therefore, you should watch before jumping on these types of artifacts each time you switch from one RDBMS to another.

Some triggers to view the actual update behavior:

 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

Expand the answer on xacinay because he is right.

You have 3 lines, and therefore @@ROWCOUNT is accurate. SQL Server changes all rows; it does not verify that the value is really different before the change, because it takes a lot of overhead to execute update commands. Just think about whether to check VARCHAR (MAX) to see if the value has really been changed or not.

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

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

It will print 3 rows of INSERTED , which is a pseudo table that contains the new values ​​for this update / insert command. The fact that the value is actually equal to b = 1 in one instance does not matter.

If you want this to be important, you need to include it in the WHERE :

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

As an alternative and as a more general way of doing this check, you can create a trigger and compare the values ​​/ fields in the DELETED table with the values ​​in the INSERTED table and use this as the basis for the row to actually be “changed”.

So - 3 is the exact number since you updated 3 lines because 3 lines were affected [a] = 1

Answer 3

I think the documentation is correct, because regardless of the fact that one line in your example already had a value of 1 in [b] , this line still met the criteria in the WHERE , so the value was updated .

We can see the proof by expanding your example a bit and including the TIMESTAMP column, as in this SQLFiddle . After updating TIMESTAMP for all columns that were mapped to claase WHERE , it changed, indicating that the row itself was changed, not just evaluated and dropped, because the target value matches what it already was.

Answer 4

In conclusion, you ask if @@ rowcount is deterministic. Indeed, if it was not deterministic, do not you think that you will see it in the documentation? It is reasonable to assume deterministic. @@ VERSION and @@ MAX_PRECISION are also not documented as deterministic, based on the fact that you question edge cases when they are not deterministic. I seriously doubt that there is a brief case that he fails, but he does, then Microsoft will accept it as an error. They are not going to return with @@ rowcount non-deterministic - the documentation does not explicitly indicate deterministic.

Numerous examples that are “affected” in TSQLQLQLQLQL count a row set to the same value (it only cares where)
And you have examples that MSSQL actually assigns the same value (timestamp)
You claim that SQL can take care of how I can do this. I know that there is no boundary case that makes cuz this is not reasonable behavior
Any program should produce consistent output
An order without sorting is not guaranteed, but the rows are the same - this is specifically documented by Microsoft and SQL as non-deterministic
If @@ rowcount was non-deterministic, then I believe Microsoft will document that it is reasonable to assume that @@ rowcount is deterministic
C # and Java do not always have the same behavior
This is no reason for me to suspect that C # and Java are not reliable.

View query plan in MSSQL
There is no predicate on [a]
On [b]

there is a predicate

If you change the request to

 UPDATE [Foo] SET [b] = 1 WHERE [a] <> 1 and [b] = 1; 

Then you will see predicates for both [a] and [b]

The query optimizer will decide how to most efficiently process a request that it is not going to modify. The predicate representation in [b] in the first query changes the query.
A valid database just does not.

I highly suspect that in MySQL, if you look at the query plan, the first query will have the predicate [a].

Instead of claiming evidence that is reliable, create an edge edge that proves that it is not reliable.
If you can create a situation, this is not reliable. I am sending Microsoft to accept it as an error.

What is a business example? If you have a business example, the value should be updated with the same value, then by definition you have something to experience.
The only examples I can do are timestamp or trigger.
If you have a real need for the update to be the same to take place, then you have something to measure.
Do you have evidence that the value is not updated to the same value?
If you still do not trust this, wrap it in a transaction.

If you do not need to upgrade to the same value, then why not just add [b] <> 1. This is more efficient.

SO - for a specific programming issue.
What is a programming issue?
Prove that MSSQL is reliable, this is not a programming issue.