sql - Update if different/changed

Question

Is it possible to perform an update statement in sql, but only update if the updates are different?

for example

if in the database, col1 = "hello"

update table1 set col1 = 'hello'

should not perform any kind of update

however, if

update table1 set col1 = "bye"

this should perform an update.

Answer 1

During query compilation and execution, SQL Server does not take the time to figure out whether an UPDATE statement will actually change any values or not. It just performs the writes as expected, even if unnecessary.

In the scenario like

update table1 set col1 = 'hello'

you might think SQL won’t do anything, but it will – it will perform all of the writes necessary as if you’d actually changed the value. This occurs for both the physical table (or clustered index) as well as any non-clustered indexes defined on that column. This causes writes to the physical tables/indexes, recalculating of indexes and transaction log writes. When working with large data sets, there is huge performance benefits to only updating rows that will receive a change.

If we want to avoid the overhead of these writes when not necessary we have to devise a way to check for the need to be updated. One way to check for the need to update would be to add something like “where col <> 'hello'.

update table1 set col1 = 'hello' where col1 <> 'hello'

But this would not perform well in some cases, for example if you were updating multiple columns in a table with many rows and only a small subset of those rows would actually have their values changed. This is because of the need to then filter on all of those columns, and non-equality predicates are generally not able to use index seeks, and the overhead of table & index writes and transaction log entries as mentioned above.

But there is a much better alternative using a combination of an EXISTS clause with an EXCEPT clause. The idea is to compare the values in the destination row to the values in the matching source row to determine if an update is actually needed. Look at the modified query below and examine the additional query filter starting with EXISTS. Note how inside the EXISTS clause the SELECT statements have no FROM clause. That part is particularly important because this only adds on an additional constant scan and a filter operation in the query plan (the cost of both is trivial). So what you end up with is a very lightweight method for determining if an UPDATE is even needed in the first place, avoiding unnecessary write overhead.

update table1 set col1 = 'hello'
/* AVOID NET ZERO CHANGES */
where exists 
    (
    /* DESTINATION */
    select table1.col1
    except
    /* SOURCE */
    select col1 = 'hello'
    )

This looks overly complicated vs checking for updates in a simple WHERE clause for the simple scenerio in the original question when you are updating one value for all rows in a table with a literal value. However, this technique works very well if you are updating multiple columns in a table, and the source of your update is another query and you want to minimize writes and transaction logs entries. It also performs better than testing every field with <>.

A more complete example might be

update table1
   set col1 = 'hello',
       col2 = 'hello',
       col3 = 'hello'
/* Only update rows from CustomerId 100, 101, 102 & 103 */
where table1.CustomerId IN (100, 101, 102, 103)
/* AVOID NET ZERO CHANGES */
  and exists 
    (
    /* DESTINATION */
    select table1.col1
           table1.col2
           table1.col3
    except
    /* SOURCE */
    select z.col1,
           z.col2,
           z.col3
      from #anytemptableorsubquery z
     where z.CustomerId = table1.CustomerId
    )