Snippet

SQL Server: Update Only When Values Change

Avoid unnecessary writes and locking by updating rows only when values differ.

Updated
Requires
SQL Server 2008+
Tags
sqlsql-serverperformancedata-fix
Markdown
View .md

Problem

A bulk update runs successfully but updates every matching row, even when the value has not changed. This increases locking, transaction log usage, and can trigger unnecessary downstream processes.

Solution

Add a comparison condition so only genuinely changed rows are updated.

UPDATE t
SET t.some_column = tu.new_some_column
FROM target_table t
JOIN #temp_updates tu ON tu.id = t.id
WHERE
    t.some_column <> tu.new_some_column
    OR t.some_column IS NULL;
Generate this change-only update with a backup and rollback in the Data Fix Script Builder Opens the tool with this example filled in

Notes

  • Reduces transaction log growth during large updates.
  • Minimises locking and index churn.
  • Particularly useful when updates trigger audit logs or change tracking.
  • Always preview with a SELECT first:
SELECT *
FROM target_table t
JOIN #temp_updates tu ON tu.id = t.id
WHERE
    t.some_column <> tu.new_some_column
    OR t.some_column IS NULL;