Synchronizing data between two SQL Server databases requires a latency target, change source, conflict rule, and recovery plan before tool selection.
SQL Server: Why INSERT OUTPUT Does Not Prove a Commit
INSERT OUTPUT can return an identifier before a transaction commits. These tested examples show a rollback after a returned row and a pattern that returns results after commit.
SQL SERVER – TRANSACTION, DML and Schema Locks
Most candidates in a senior developer interview could not create a scenario that shows schema locks. Here is a simple demo with a TRANSACTION and DML.
Sequence Gaps: Why NEXT VALUE FOR Skips Numbers
Explain sequence gaps, inspect cached values, test rollback behavior, and separate SQL Server identifiers from gapless numbering.
UPDATE With a JOIN: Changing Rows From Another Table Safely
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.