Is SQL Server smart enough not to UPDATE if values are the same?

Question

At work we've been hacking away at a stored procedure and we noticed something.

For one of our update statements, we noticed that if the values are the same as the previous values we had a performance gain.

We were not saying

UPDATE t1 SET A=5

where the column was already was equal to 5. We were doing something like this:

UPDATE t1 SET A = Qty*4.3

Anyway, is SQL Server smart enough not to do the operation if the values evaluate to the same in an UPDATE operation or am I just being fooled by some other phenomena?

Pallua

Answer 1

Yes you will see some performance gains. I recommend reading this article to get a better understanding (it will explain why much better than I can):

https://sqlkiwi.blogspot.com/2010/08/the-impact-of-non-updating-updates.html

Harlequinade

Answer 2

Judging from TSQLs output, it considers the UPDATE executed even if it's to the same value.

CREATE TABLE test (id INT, val int);
GO

INSERT INTO test VALUES(1, 1);
GO
(1 row(s) affected)

UPDATE test SET val=1 WHERE id=1;
GO
(1 row(s) affected)

When it comes to the actual write to disk, I'd certainly hope that it's not needed.

Edit: See the answer from @AbeMiessler for a more in depth analysis how the write to log/disk part works.

Anny

Great analysis of the problem, btw – Serica

I don't think the write to disk occurs immediately. I think the question is "does the page get flagged as dirty" – Catoptrics

@ChrisDiver Good point, SQL manager isn't showing that statistic that I can find, so removing that part and deferring to the link in the answer from AbeMiessler for the definite answer. – Anny

Answer 3

You might be seeing some performance gains based on the specific state of your table indexes.

If the table is indexed, and the update doesn't require that any data is moved around (clustered) or no indexes need to be altered (non-clustered), then you might see a gain.

If you tell SQL to update, it's gonna update. So, I'd look towards the hardware side (e.g., indexing).

Identify

Good background context points raised! – Idolah

Answer 4

I had to run my own test to see. I thought I'd share the results.

First, yes, an update does occur even if the value is the same.

I created a test table with these columns:
Key
Value
LastUpdatedTime

Then I inserted:

INSERT INTO KeyValue VALUES ('TestKey1', 'TestValue1', GETDATE())

Then I created a trigger to update LastUpdatedTime when an update occurs:

CREATE TRIGGER UpdateLastUpdatedTime 
   ON  KeyValue
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    DECLARE @key VARCHAR(50) = (SELECT [Key] FROM INSERTED)
    UPDATE KeyValue SET LastUpdatedTime = GETDATE() WHERE [Key] = @key
END

Then I did an update to a column and set it to the same value it already was:

UPDATE KeyValue Set [Value] = 'TestValue1' WHERE [Key] = 'TestKey1'

The LastUpdatedTime did indeed get updated, so the trigger fired.

Now, I guess it's possible an UPDATE doesn't occur if there is no trigger, but I would guess it still does, especially since the trigger is an AFTER UPDATE.

Pula

Answer 5

SQL will have to actually calculate the numerical result before it does anything else, it has to do this so it knows what value it's got to "do something" with. Even then it would need to read the value from the table to do a comparison.

What I'm trying to say is that if this was the case, it would actually make it less efficient to read the value, compare it to the one you're trying to update it to and then make a decision as to whether it should commit to the update operation. In your case it has to read Qty before it can work out what it needs to put in the A field, but even so, by the time it's compared values it might as well has completed the update and got on with the rest of its busy day :)

Recur

I disagree. For an update it has to perform a read, to confirm that there is in fact a row to update and to slap a lock on the row(s), and possibly indexes as well, before it can issue the update. So, if the read gets done anyway, and it determines that no data is actually being changed, it can skip the actual write phase. – Percutaneous