Use UPDATE with a JOIN safely, reject duplicate source keys, compare subquery and MERGE behavior, and verify the affected rows.
MERGE Statement Pitfalls
MERGE statement pitfalls include duplicate source keys, concurrency races, trigger surprises, and complex actions that deserve testing.
SQL SERVER – Unique Nonclustered Index Creation with IGNORE_DUP_KEY = ON – A Transactional Behavior
When is IGNORE_DUP_KEY = ON useful? Jacob Sebastian and I built an example that shows its transactional behavior when you insert only nonduplicate rows.
SQL SERVER – Difference Temp Table and Table Variable – Effect of Transaction
Temp table versus table variable, part two: a transaction rollback undoes changes to a temp table, but a table variable keeps its values.
Following a Blocking Chain to the Head Blocker
Follow SQL Server blocking_session_id through a blocking chain, identify the head blocker, and choose a safe ending for the wait.
Preventing Overlapping Date Ranges in a Table
Detect overlapping date ranges with joins and window queries, use half-open endpoints, and enforce the rule with a multirow trigger.
Finding Sleeping Sessions That Still Hold Locks
Find sleeping sessions with open transactions, inspect their locks and last batch, and identify the application owner before ending work.