Matching source and target values can still generate a write. No-op updates can still take locks, fire triggers, and change rowversion values. Compare source and target before updating so the statement touches only rows whose relevant values actually differ.
Define the Real Difference Behind No-Op Updates
An UPDATE that assigns the existing value is still an update statement against that row. It can trigger related work and change a rowversion column. Physical logging details depend on the operation, but unnecessary updates add work and complicate auditing even when the business value remains the same.
I check the comparison rule before changing a synchronization process. Which columns belong to the synchronized payload? Which columns belong to the target's own audit or workflow state? Comparing the wrong set can either miss a real change or rewrite a row unnecessarily.
NULL needs special treatment. A conventional inequality does not return true when one value is NULL and the other is populated. The SQL three-valued logic matters here. A synchronization job that ignores this case can leave target data stale while reporting a successful run. Unchanged rows do not need another motivational update from the nightly job.
Skip no-op updates only after checking whether triggers or application logic depend on receiving the attempted write.
Build Source and Target With Deliberate Cases
The temporary tables below contain equal values, a changed text value, and a NULL-to-value change. They are synthetic test inputs. The target includes rowversion so you can inspect the side effect of touching a row. Run all blocks in the same connection.
The source primary key ensures one source row per target key. Multiple source matches make update semantics ambiguous and should be rejected before the statement runs. A real synchronization process also needs its own policy for missing target rows and deleted source rows. This article focuses on updates to matching keys.
I keep the first test deliberately small and readable. You should be able to predict which identifiers need a change before executing the query. A large test set is useful later for cost, but it is a poor substitute for a clear correctness test. Include each boundary condition in a separate identifiable row.
CREATE TABLE #SyncTarget(ID int PRIMARY KEY,NameText nvarchar(40),Amount decimal(12,2),VersionToken rowversion);
CREATE TABLE #SyncSource(ID int PRIMARY KEY,NameText nvarchar(40),Amount decimal(12,2));
INSERT #SyncTarget(ID,NameText,Amount) VALUES(1,N'Equal',10),(2,N'Old',20),(3,NULL,30);
INSERT #SyncSource VALUES(1,N'Equal',10),(2,N'New',20),(3,N'Present',30);
SELECT * FROM #SyncTarget ORDER BY ID;Use EXCEPT for NULL-Safe Row Comparison
EXCEPT compares two projected rows and treats corresponding NULL values as equal for its distinct comparison. If the source payload differs from the target payload, the source projection survives the EXCEPT. EXISTS then makes that surviving row the condition for UPDATE.
The next statement compares the two payload columns as a pair. Keep their order and compatible types aligned. The comparison inherits relevant collation and type-conversion rules, so make those intentional too. Case-sensitive business changes require the appropriate string comparison rule.
Capture the affected count immediately after UPDATE. The script returns both the count and the target values for your own inspection. It does not claim a measured production improvement. Rerun the same statement after the data matches and check its behavior. The second execution is a useful test of idempotence because matching rows should remain untouched under the defined comparison.
DECLARE @Changed bigint;
UPDATE t SET NameText=s.NameText,Amount=s.Amount
FROM #SyncTarget AS t
JOIN #SyncSource AS s ON s.ID=t.ID
WHERE EXISTS(SELECT s.NameText,s.Amount EXCEPT SELECT t.NameText,t.Amount);
SET @Changed=ROWCOUNT_BIG();
SELECT @Changed AS ChangedRows;
SELECT * FROM #SyncTarget ORDER BY ID;
Use IS DISTINCT FROM on SQL Server 2022
SQL Server 2022 adds IS DISTINCT FROM, which expresses NULL-safe difference directly. A pair of NULL values is not distinct, while a NULL and a non-NULL value are distinct. That removes the extra projection when a small number of individual column comparisons reads more clearly.
The next query changes a synthetic source value, then updates matching target rows when either payload column differs. The operator is version-specific, so use the EXCEPT form on earlier supported versions. Both approaches still need compatible data types and the correct comparison semantics.
Which form will the next maintainer understand more easily? Choose the clear form supported by your deployment target. Do not mix NULL replacement tricks such as substituting an empty string unless the business truly treats those values as equal. A convenient sentinel can collide with valid input and silently turn a real difference into an apparent match.
UPDATE #SyncSource SET Amount=21 WHERE ID=2;
UPDATE t SET NameText=s.NameText,Amount=s.Amount
FROM #SyncTarget AS t
JOIN #SyncSource AS s ON s.ID=t.ID
WHERE s.NameText IS DISTINCT FROM t.NameText
OR s.Amount IS DISTINCT FROM t.Amount;
SELECT ROWCOUNT_BIG() AS ChangedRows;Consider Hashes Only With a Defined Payload
Comparing many wide columns consumes CPU. A stored HASHBYTES representation can reduce repeated comparison work, but it creates another data contract. The serialized payload must preserve column boundaries, NULL distinctions, types, lengths, and the business equality rules. Naively concatenating values does not do that reliably.
Use a suitable supported hash algorithm and account for the possibility of collisions. A hash is a comparison aid, not an absolute replacement for correctness requirements. Where correctness demands it, verify the actual columns for candidate differences or retain another explicit validation strategy.
Measure the full write path as well as the comparison. Persisting or calculating a hash has its own cost, especially when source rows change frequently. A two-column example does not need a hash column merely because hashing sounds efficient. Start with the clear NULL-safe predicate and add complexity only when representative measurements justify it.
Verify That No-Op Updates Stay Skipped on Rerun
Check triggers, rowversion, audit records, and transaction-log behavior under the real table design. A reduced affected count is useful evidence, but it does not describe every side effect. Existing triggers also need to handle multirow updates correctly. The filtered statement changes which rows enter those trigger sets.
Test matching rows, one changed column, several changed columns, NULL transitions, and duplicate source keys. Compare source and target after the operation, then rerun it to verify matching rows stay untouched. Keep concurrency and transaction boundaries consistent with the synchronization contract.
Skipping no-op updates makes the change count more meaningful and reduces avoidable work. It does not eliminate the need for correct inserts, deletions, or conflict handling. Define the payload, compare it with NULL-safe semantics, and let the update statement focus on the rows whose synchronized values actually changed.
Related reading on this blog: UPDATE() in a Trigger Is True Even When Nothing Changed and UPDATE Without WHERE Clause: The Day Everyone Got a Raise.
A synchronized row is not one that was updated again, it is one whose relevant values already match.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
