7 questions found
What does the MERGE statement do in SQL Server, and why is it called an upsert operation?
Beginner
The MERGE statement lets you compare a source set of data against a target table and, in a single statement, insert new rows that do not yet exist, update existing rows that have changed, and optionally delete rows that no longer appear in the source, which is why it is commonly called an upsert since it combines update and insert logic together.
MERGE INTO Products AS target
USING StagingProducts AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN UPDATE SET target.Price = source.Price
WHEN NOT MATCHED THEN INSERT (ProductId, Price) VALUES (source.ProductId, source.Price);
Real-world example
A nightly product synchronization job uses MERGE to update prices for existing products and insert brand new products from a staging table, all in a single, clean statement instead of writing separate insert and update logic.
Common follow-ups: What is the difference between WHEN MATCHED and WHEN NOT MATCHED in a MERGE statement?;Can MERGE also handle deleting rows that no longer exist in the source?
Data Types & Schema Design;Transactions & ACID
What are the three main clauses of a MERGE statement, and what does each one control?
Beginner
WHEN MATCHED handles rows that exist in both the target and source, typically used for updates, WHEN NOT MATCHED BY TARGET handles rows that exist in the source but not the target, typically used for inserts, and WHEN NOT MATCHED BY SOURCE handles rows that exist in the target but not the source, often used to delete rows that are no longer present in the incoming data.
MERGE INTO Inventory AS target
USING NewInventory AS source
ON target.ItemId = source.ItemId
WHEN MATCHED THEN UPDATE SET target.Quantity = source.Quantity
WHEN NOT MATCHED BY TARGET THEN INSERT (ItemId, Quantity) VALUES (source.ItemId, source.Quantity)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
Real-world example
An inventory synchronization process updates existing item quantities, adds any brand new items, and removes items that no longer appear in the latest inventory feed, all handled cleanly through the three clauses of a single MERGE statement.
Common follow-ups: Is it mandatory to include all three WHEN clauses in every MERGE statement?;What happens if a row matches multiple source rows unexpectedly?
Data Types & Schema Design;Constraints (Primary Key
Foreign Key
Check & Unique)
What are some known issues or caveats with the MERGE statement that developers should be aware of before relying on it heavily?
Intermediate
The MERGE statement has historically had a few documented bugs related to trigger behavior and concurrency under certain isolation levels, and Microsoft has recommended being cautious in some high concurrency scenarios, so many teams thoroughly test MERGE statements under realistic concurrent load or consider separate INSERT and UPDATE statements wrapped in a transaction as an alternative for critical systems.
-- Some teams prefer explicit separate statements
-- for critical, high concurrency scenarios
BEGIN TRANSACTION;
UPDATE Products SET Price = @Price WHERE ProductId = @Id;
IF @@ROWCOUNT = 0
INSERT INTO Products (ProductId, Price) VALUES (@Id, @Price);
COMMIT TRANSACTION;
Real-world example
A team building a high concurrency payment system chooses to use separate, carefully tested INSERT and UPDATE statements instead of MERGE, after reading about known edge case issues with MERGE under certain concurrent conditions.
Common follow-ups: What specific concurrency issues have been documented with MERGE in the past?;How do you decide whether MERGE is safe enough for a specific use case?
Isolation & Locking;Transactions & ACID
How do you use the OUTPUT clause together with a MERGE statement to capture which rows were inserted, updated, or deleted?
Intermediate
You add an OUTPUT clause to your MERGE statement, referencing the special $action column along with the inserted and deleted pseudo tables, which lets you capture exactly what happened to each row, such as logging every insert, update, and delete performed by the MERGE for auditing purposes.
MERGE INTO Products AS target
USING StagingProducts AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN UPDATE SET target.Price = source.Price
WHEN NOT MATCHED THEN INSERT (ProductId, Price) VALUES (source.ProductId, source.Price)
OUTPUT $action, inserted.ProductId, inserted.Price;
Real-world example
A pricing synchronization job captures a detailed log of exactly which products were updated versus newly inserted during each run, using the OUTPUT clause to feed that information into an audit table for later review.
Common follow-ups: Can the OUTPUT clause from a MERGE statement be inserted directly into another table?;What does the $action column actually contain for each type of operation?
Change Data Capture (CDC);Data Types & Schema Design
How would you use MERGE to implement a slowly changing dimension pattern in a data warehouse, tracking historical changes to a record over time?
Advanced
You would use MERGE to compare incoming dimension data against the current table, closing out the previous version of a changed record by setting an end date and marking it inactive when a match is found with different values, while inserting a brand new row representing the current version of that record, preserving a full history of changes over time.
MERGE INTO CustomerDimension AS target
USING StagingCustomers AS source
ON target.CustomerId = source.CustomerId AND target.IsCurrent = 1
WHEN MATCHED AND target.Address <> source.Address THEN
UPDATE SET target.IsCurrent = 0, target.EndDate = GETDATE()
WHEN NOT MATCHED THEN
INSERT (CustomerId, Address, IsCurrent, StartDate) VALUES (source.CustomerId, source.Address, 1, GETDATE());
Real-world example
A data warehouse tracks a complete history of customer address changes over time using a slowly changing dimension pattern built with MERGE, allowing analysts to see exactly what a customer's address was at any point in the past.
Common follow-ups: What is a Type 2 slowly changing dimension, and how does this pattern implement it?;How do you handle inserting the new current version after closing out the old one in the same MERGE statement?
Data Types & Schema Design;Normalization
What performance considerations should you keep in mind when using MERGE against very large source and target tables?
Advanced
Ensure both the source and target tables have appropriate indexes on the columns used in the ON clause matching condition, since MERGE needs to efficiently find matching rows between the two sets, and consider processing extremely large merges in smaller batches rather than a single massive statement, which can reduce lock contention and transaction log growth.
-- Ensure the join column used in the MERGE ON clause is indexed
CREATE INDEX IX_StagingProducts_ProductId ON StagingProducts(ProductId);
Real-world example
A large retailer processing millions of product updates nightly ensures both their staging and target product tables have proper indexes on the matching column, significantly reducing the time their nightly MERGE operation takes to complete.
Common follow-ups: How does batch size affect the performance of a large MERGE operation?;What locking behavior should you expect during a very large MERGE statement?
Query Optimization & Plans;Indexes
How do you safely test a MERGE statement before running it against real production data?
Intermediate
You can wrap the MERGE statement in a transaction, run it, review the affected row counts and results using the OUTPUT clause, and then roll back the transaction to undo the changes, giving you a safe way to verify the exact behavior of a complex MERGE statement before committing to running it against your actual production data.
BEGIN TRANSACTION;
MERGE INTO Products AS target
USING StagingProducts AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN UPDATE SET target.Price = source.Price
OUTPUT $action, inserted.ProductId;
-- Review the output, then decide
ROLLBACK TRANSACTION;
Real-world example
A developer tests a new MERGE statement against a copy of production data inside a transaction, reviewing the OUTPUT results carefully before rolling back, then finally running the confirmed correct statement for real once satisfied.
Common follow-ups: Is it safe to test destructive statements like MERGE directly against production data this way?;What should you check in the OUTPUT results before trusting the MERGE logic is correct?
Transactions & ACID;Error Handling with TRY CATCH