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?

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

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.