All 7 Merge Statement (Upsert) questions
More in Merge Statement (Upsert)
What are some known issues or caveats with the MERGE statement that developers should be aware of before relying on it heavily? How do you use the OUTPUT clause together with a MERGE statement to capture which rows were inserted, updated, or deleted? How do you safely test a MERGE statement before running it against real production data? How would you use MERGE to implement a slowly changing dimension pattern in a data warehouse, tracking historical changes to a record over time?
Advertisement
SQL Server › Merge Statement (Upsert)

What performance considerations should you keep in mind when using MERGE against very large source and target tables?

Advanced

Answer

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
All 7 Merge Statement (Upsert) questions