'Update a column only when having a different value

Update table1 set Name='Deepak' where id=1 and Name !='Deepak'

Does adding a condition on name column improves the performance considering that id has clustered index and Name has non clustered and there is around 60% probability of getting '0 rows updated' message after running the above query.

sql-servertsql


Solution 1:[1]

Reasons to Only Update If Different

  • Reduced locks
  • Prevents unnecessary activity if you have triggers or certain configs of SQL Server Replication
  • Preserve audit trail columns like LastModifiedDateTime

Only Update If Different Using EXCEPT

Most people's main complaint would probably be extra query complexity, but I find using EXCEPT makes this process super easy. EXCEPT is ideal because it handles any data type and NULL values without issue

UPDATE Table1
SET Col1 = @NewVal1
,Col2 = @NewVal2
....
,LastModifiedBy = @UserID
,LastModifiedDateTime = GETDATE()
WHERE EXISTS (
                SELECT Col1,Col2
                EXCEPT
                SELECT @NewVal1,@NewVal2
            )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stephan

Related Questions