How to update a table only if the update results in a change of values?

Question

I use system-versioned tables in my SQL Server database, which has a number of tables with parent/child relationships. I am writing a stored procedure to update a set of parent/child tables using parameters supplied to the procedure.

With system-versioned tables, a new row will be created in the history table whenever an UPDATE command is run, even if none of the values have changed. To avoid creating numerous rows in my history table that are identical to rows in my temporal table, I would like this SP to only update a table when a value supplied by a parameter is different from a value that already exists in the table row.

Based on similar questions I've seen, I know I could include something in the WHERE statement to do this (i.e., WHERE @Param <> [Value]); however, I will be dealing with a large number of different tables and values, so I was hoping to find a more scalable solution.

Answer 1

I'm not a fan of using @Param <> ColumnName syntax for something like this; if either value is NULL then this expression will resolve the UNKNOWN, which isn't TRUE, and so the row isn't affected. The more complete version would be:

WHERE (@Param <> ColumnName
   OR  (@Param IS NULL AND ColumnName IS NOT NULL)
   OR  (@Param IS NOT NULL AND ColumnName IS NULL))

For a lot of NULLable columns that becomes very long.

In newer versions of SQL Server you can use IS DISTINCT FROM instead, which will treat an expression like NULL IS DISTINCT FROM 1 as TRUE and NULL IS DISTINCT FROM NULL as FALSE.

This, much like the solution you have, involves a lot of OR clauses:

...
FROM ...
WHERE @Param1 IS DISTINCT FROM Col1
   OR @Param2 IS DISTINCT FROM Col2
   OR @Param3 IS DISTINCT FROM Col3
   ...
   OR @Param15 IS DISTINCT FROM Col15

An alternative method I quite like is to use an EXCEPT or INTERSECT in a (NOT) EXISTS), and SELECT the parameters in one and the columns in the other. This would look something like this:

...
FROM ...
WHERE EXISTS (SELECT Col1, Col2, Col3, ..., Col15
              EXCEPT
              SELECT @Param1, @Param2, @Param3, ..., @Param15);

If all the columns and parameters have the same value then a row won't be returned in the subquery, and so the EXISTS returns FALSE and the row isn't updated. Like IS DISTINCT FROM, this will handle NULL values, as SELECT NULL EXCEPT SELECT NULL; will result in no returned rows.