SQL Server MERGE: Why the Community Argues About It and What to Use Instead

---

The MERGE statement has been in SQL Server since 2008. It promises to combine INSERT, UPDATE, and DELETE into one clean atomic statement. It looks elegant. It reads like plain English. And it has generated more heated debate in the SQL Server community than almost any other feature.

The debate is not purely academic. MERGE has a documented history of bugs and race conditions that have cost production systems real data integrity. Some of those bugs are fixed in current versions. Some require workarounds to use safely. And the workarounds introduce their own trade-offs. Understanding what MERGE actually does, what can go wrong, and what the alternatives look like is the point of this article.

1 What MERGE Does and Why It Looks Attractive Beginner

MERGE takes a source dataset and a target table, joins them on a condition, and performs different actions depending on whether each source row matches a target row. A typical MERGE statement does one of three things for each source row: updates the target row if a match exists, inserts a new row if no match exists, or optionally deletes a target row that has no corresponding source row.

-- The classic MERGE pattern: upsert a single row
MERGE dbo.Products AS target
USING (SELECT @ProductID, @ProductName, @Price) AS source (ProductID, ProductName, Price)
    ON target.ProductID = source.ProductID
WHEN MATCHED THEN
    UPDATE SET
        target.ProductName = source.ProductName,
        target.Price       = source.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price)
    VALUES (source.ProductID, source.ProductName, source.Price);

-- MERGE also works for bulk synchronization
-- Update or insert many rows from a staging table at once
MERGE dbo.Products AS target
USING dbo.ProductsStage AS source
    ON target.ProductID = source.ProductID
WHEN MATCHED THEN
    UPDATE SET
        target.ProductName = source.ProductName,
        target.Price       = source.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price)
    VALUES (source.ProductID, source.ProductName, source.Price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;  -- remove target rows not present in source

The appeal is real. One statement does the work of three. The logic is explicit and readable. The OUTPUT clause works with MERGE and can capture which rows were inserted, updated, or deleted. For ETL and data warehouse staging loads where you need to synchronize a target table with a source dataset, MERGE appears to be exactly the tool for the job.

The problems start when you look at what SQL Server actually does internally when MERGE executes, and what happens under concurrent load.

2 The Race Condition: Why MERGE Is Not as Atomic as It Appears Intermediate

MERGE looks atomic. It is one statement. Most developers assume that one statement means one operation that either fully succeeds or fully fails with no window for concurrent interference. That assumption is incorrect for MERGE under concurrent load with default isolation settings.

Internally, MERGE must first read the target table to determine which rows match the source and which do not. It then executes the appropriate data modification for each case. Between the read phase and the write phase, under default READ COMMITTED isolation, the locks taken during the read can be released before the write occurs. This creates a window where another concurrent session can modify the rows MERGE read, changing the ground truth that MERGE based its MATCHED or NOT MATCHED determination on.

The practical result is primary key violations and race conditions. Two sessions both execute the same MERGE against the same table at the same time. Both read the target, both determine that a particular row does not exist, both proceed to the INSERT branch, and both attempt to insert the same key. One succeeds and one fails with a primary key violation. The statement that appears atomic is not.

-- Demonstrating the race condition
-- Session 1 and Session 2 both execute this at the same time:
MERGE dbo.Products AS target
USING (SELECT 101 AS ProductID, 'Widget' AS ProductName, 9.99 AS Price) AS source
      (ProductID, ProductName, Price)
    ON target.ProductID = source.ProductID
WHEN MATCHED THEN
    UPDATE SET target.Price = source.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price)
    VALUES (source.ProductID, source.ProductName, source.Price);

-- Without HOLDLOCK:
-- Both sessions read: ProductID 101 does not exist
-- Both sessions enter the INSERT branch
-- Session 1 inserts: succeeds
-- Session 2 inserts: PRIMARY KEY VIOLATION
-- Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key.

-- This is not a contrived edge case.
-- It happens in production at high-concurrency OLTP systems.
-- Aaron Bertrand has noted in his published work that in reviewing customer
-- code he rarely encounters HOLDLOCK naturally, meaning most MERGE
-- statements in production are vulnerable to this race condition.

3 HOLDLOCK: The Required Fix and Its Cost Intermediate

The fix for the race condition is to add the HOLDLOCK hint to the MERGE target. HOLDLOCK causes the locks taken during the read phase to be held until the transaction completes rather than being released early. This prevents the concurrent modification window that creates the race condition.

-- MERGE with HOLDLOCK: the required pattern for safe concurrent use
MERGE dbo.Products WITH (HOLDLOCK) AS target
USING (SELECT @ProductID, @ProductName, @Price) AS source (ProductID, ProductName, Price)
    ON target.ProductID = source.ProductID
WHEN MATCHED THEN
    UPDATE SET
        target.ProductName = source.ProductName,
        target.Price       = source.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price)
    VALUES (source.ProductID, source.ProductName, source.Price);

-- HOLDLOCK is equivalent to SERIALIZABLE isolation for the duration of this statement
-- The locks are held, the race condition is eliminated
-- The cost: higher lock granularity, longer lock hold duration, potential for deadlocks

HOLDLOCK forces the statement to run at SERIALIZABLE isolation, which means range locks are taken on the key range being searched. This prevents phantom inserts by other sessions into the range MERGE is examining. The race condition is genuinely eliminated with HOLDLOCK in place.

The cost is that SERIALIZABLE isolation is the most restrictive isolation level. Range locks are more expensive than the standard shared locks taken at READ COMMITTED. Lock hold duration is longer. Under high concurrency, this increases the probability of blocking and deadlocks, particularly when multiple sessions are merging on overlapping or adjacent key ranges.

! Special Case: MERGE Across Linked Servers Advanced

If the MERGE race condition on a local table is bad, MERGE across a linked server without lock hints is worse. The distributed nature of linked server queries adds additional complexity to the locking model. Lock hints that work correctly on local tables do not always behave identically across a linked server connection, and the window for race conditions widens because of the additional network round trips involved in distributed query execution.

This is not a theoretical concern. Brent Ozar posted on June 4, 2026 that he had found MERGE in production with no lock hints going across linked servers. That is the most dangerous combination: no HOLDLOCK, distributed execution, and concurrent access. If you find MERGE across a linked server in your environment, treat it as a priority fix regardless of whether it has caused visible problems yet.

-- If you must use MERGE across a linked server, HOLDLOCK is still required
-- Test this thoroughly -- distributed query locking behavior can differ
-- from local table behavior in ways that are not always predictable

MERGE [LinkedServerName].[DatabaseName].[dbo].[TargetTable] WITH (HOLDLOCK) AS target
USING (SELECT @Key, @Value) AS source (KeyCol, ValueCol)
    ON target.KeyCol = source.KeyCol
WHEN MATCHED THEN
    UPDATE SET target.ValueCol = source.ValueCol
WHEN NOT MATCHED BY TARGET THEN
    INSERT (KeyCol, ValueCol)
    VALUES (source.KeyCol, source.ValueCol);

-- Strongly preferred over the above: avoid MERGE across linked servers entirely
-- Use explicit UPDATE then INSERT with a proper transaction instead
-- The combination of MERGE semantics and distributed query execution
-- adds complexity that is not worth the syntactic convenience

4 The HOLDLOCK and RCSI Deadlock Problem Advanced

HOLDLOCK introduces a second problem when the database uses Read Committed Snapshot Isolation (RCSI). RCSI is one of the most commonly enabled optimizations in SQL Server environments because it eliminates the reader-writer blocking that plagues standard READ COMMITTED. Under RCSI, readers take no locks and writers do not block readers.

The problem is that HOLDLOCK forces SERIALIZABLE isolation, which takes range locks. RCSI does not protect SERIALIZABLE range locks from causing deadlocks. The combination of HOLDLOCK on a MERGE statement and RCSI at the database level has been reported to cause deadlocks under concurrency that would not occur with either setting alone.

The resolution depends on your workload pattern:

• If you need both RCSI and safe MERGE, replace the MERGE with explicit UPDATE and INSERT statements inside an explicit SERIALIZABLE transaction with retry logic for deadlocks
• If HOLDLOCK is causing deadlocks on a RCSI database, removing HOLDLOCK and replacing the MERGE with the patterns in Sections 8 and 9 is the cleanest resolution
• If you must keep MERGE with HOLDLOCK on RCSI, the workaround is explicit retry logic in the application or stored procedure

-- Checking whether your database has RCSI enabled
-- HOLDLOCK + RCSI can cause deadlocks on MERGE statements

SELECT
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();

-- If is_read_committed_snapshot_on = 1 and you are using MERGE with HOLDLOCK:
-- Review your deadlock patterns -- the combination is a known deadlock source

5 The Bug History Intermediate

Beyond the race condition, MERGE accumulated a documented list of bugs over the years following its introduction in SQL Server 2008. Aaron Bertrand’s frequently cited MSSQLTips article compiled this list and it became the primary reference the community pointed to when arguing against MERGE.

The bugs covered a range of behaviors including incorrect trigger firing, wrong results under certain execution plans, issues with certain data types, and problems with specific syntax combinations. Paul White has documented several MERGE-specific bugs in technical depth, including optimizer behavior and plan shape issues that produce incorrect results in specific edge cases.

In September 2023, Hugo Kornelis of SQLServerFast.com independently investigated the full list. His published conclusion differs from Bertrand’s and Swart’s in an important way: he found that many of the bugs on the list had been fixed in current versions of SQL Server, and that some items on the list were not actual bugs but documentation feedback or behavior that applied equally to other statements. His conclusion was that the core concern with MERGE in modern SQL Server (2022 CU7 and later) is primarily the race condition addressed by HOLDLOCK rather than an ongoing list of unfixed bugs.

His final position was more permissive than Bertrand’s and Swart’s: MERGE with HOLDLOCK is the requirement, and for many workloads on current SQL Server versions, MERGE with HOLDLOCK is safe. He explicitly disagreed with the blanket “avoid MERGE altogether” guidance.

Both positions are held by experienced and credible SQL Server professionals based on independent research. The honest answer is that the right choice depends on your SQL Server version, whether RCSI is enabled, your workload’s concurrency pattern, and how comfortable your team is with the HOLDLOCK requirement being consistently applied.

6 Where the Community Stands in 2026 Beginner

The community consensus in 2026 leans toward caution but is not unanimous. The practical summary across the published positions is:

• If you use MERGE for concurrent upsert operations, HOLDLOCK is non-negotiable. A MERGE without HOLDLOCK on a table receiving concurrent writes is a bug.
• If your database uses RCSI, test your MERGE with HOLDLOCK pattern specifically for deadlocks under concurrent load before deploying to production.
• If you are on SQL Server 2022 with current cumulative updates applied, the historical bug list is significantly shorter than it was in 2018 when the most-cited articles were written.
• For single-row upsert operations at high concurrency, the explicit UPDATE then INSERT patterns in Sections 8 and 9 are safer, more readable, and easier to troubleshoot than MERGE in most code reviews.
• For batch synchronization of large datasets (ETL, staging table loads), MERGE with HOLDLOCK remains a reasonable tool when used correctly on current SQL Server versions.

7 The IF EXISTS Then UPDATE Else INSERT Anti-Pattern Intermediate

When developers move away from MERGE they often reach for this pattern first. It reads clearly, it is easy to understand, and it appears to handle the existence check explicitly. Under concurrent load it fails in exactly the same way MERGE fails without HOLDLOCK.

-- This pattern looks safe but is NOT safe under concurrent load
-- Aaron Bertrand published analysis of this anti-pattern at SQLPerformance.com

IF EXISTS (SELECT 1 FROM dbo.Products WHERE ProductID = @ProductID)
BEGIN
    UPDATE dbo.Products
    SET    ProductName = @ProductName,
           Price       = @Price
    WHERE  ProductID   = @ProductID;
END
ELSE
BEGIN
    INSERT INTO dbo.Products (ProductID, ProductName, Price)
    VALUES (@ProductID, @ProductName, @Price);
END

-- Under concurrent load:
-- Session 1: EXISTS check returns false (row does not exist)
-- Session 2: EXISTS check returns false (row does not exist)
-- Session 1: executes INSERT, succeeds
-- Session 2: executes INSERT, PRIMARY KEY VIOLATION
-- This is identical to the MERGE race condition.
-- Switching from MERGE to this pattern does not fix the concurrency problem.
-- It just makes you feel better about it until load increases.

The IF EXISTS pattern also performs more work than necessary in many cases. It executes a SELECT before the modification operation, adding an extra round trip regardless of whether the row exists. Both the update-first and insert-first patterns in the following sections avoid this overhead.

8 Safe UPSERT Pattern: Update-First Intermediate

The update-first pattern attempts the UPDATE and checks whether it affected any rows. If it did not affect any rows, the row did not exist and an INSERT is needed. This pattern is safe under concurrent load for most workloads and avoids the HOLDLOCK cost when the majority of operations are updates rather than inserts.

-- SAFE: Update-first upsert pattern
-- Best when most operations are updates (row usually already exists)
-- Michael J. Swart and Aaron Bertrand both discuss this pattern in their published work

BEGIN TRANSACTION;

    UPDATE dbo.Products
    SET    ProductName = @ProductName,
           Price       = @Price
    WHERE  ProductID   = @ProductID;

    IF @@ROWCOUNT = 0
    BEGIN
        -- Row did not exist, insert it
        -- Under very high concurrency, two sessions can reach this
        -- INSERT at the same time. Handle with TRY/CATCH if needed.
        INSERT INTO dbo.Products (ProductID, ProductName, Price)
        VALUES (@ProductID, @ProductName, @Price);
    END

COMMIT TRANSACTION;

-- For high-concurrency environments add TRY/CATCH around the INSERT
-- to handle the rare concurrent INSERT case gracefully:
BEGIN TRANSACTION;

    UPDATE dbo.Products
    SET    ProductName = @ProductName,
           Price       = @Price
    WHERE  ProductID   = @ProductID;

    IF @@ROWCOUNT = 0
    BEGIN
        BEGIN TRY
            INSERT INTO dbo.Products (ProductID, ProductName, Price)
            VALUES (@ProductID, @ProductName, @Price);
        END TRY
        BEGIN CATCH
            -- Primary key violation: concurrent insert won the race
            -- The row now exists. Retry the UPDATE.
            IF ERROR_NUMBER() = 2627  -- primary key violation
            BEGIN
                UPDATE dbo.Products
                SET    ProductName = @ProductName,
                       Price       = @Price
                WHERE  ProductID   = @ProductID;
            END
            ELSE
                THROW;  -- unexpected error, re-raise
        END CATCH
    END

COMMIT TRANSACTION;

9 Safe UPSERT Pattern: Insert-First with TRY CATCH Intermediate

The insert-first pattern attempts the INSERT and handles the primary key violation if the row already exists by falling through to an UPDATE. This pattern is safe under concurrent load and performs better than the update-first pattern when most operations are inserts rather than updates.

-- SAFE: Insert-first upsert pattern
-- Best when most operations are inserts (row usually does not yet exist)

BEGIN TRANSACTION;

    BEGIN TRY
        -- Attempt insert first
        INSERT INTO dbo.Products (ProductID, ProductName, Price)
        VALUES (@ProductID, @ProductName, @Price);
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 2627  -- primary key violation: row already exists
        BEGIN
            -- Row exists, update it instead
            UPDATE dbo.Products
            SET    ProductName = @ProductName,
                   Price       = @Price
            WHERE  ProductID   = @ProductID;
        END
        ELSE
            THROW;  -- unexpected error, re-raise
    END CATCH

COMMIT TRANSACTION;

-- Note: error number 2627 is primary key violation
-- error number 2601 is unique constraint violation (use OR in the IF condition)
-- IF ERROR_NUMBER() IN (2627, 2601)

-- This pattern handles concurrent inserts correctly:
-- Two sessions both try to INSERT the same key simultaneously
-- One succeeds, one gets the primary key violation and falls to the UPDATE path
-- Both complete correctly with no unhandled errors

Choosing Between Update-First and Insert-First

Consideration	Update-First	Insert-First
Workload pattern	Most operations are updates (row usually exists)	Most operations are inserts (row usually new)
Performance on existing rows	One statement: UPDATE hits directly	Two statements: INSERT fails then UPDATE
Performance on new rows	Two statements: UPDATE misses then INSERT	One statement: INSERT succeeds directly
Concurrent insert handling	Requires TRY/CATCH on the INSERT for race condition	INSERT failure is expected and handled naturally
Readability	Clear intent: update if exists, insert if not	Less intuitive: attempt insert, handle failure

10 When MERGE Is Actually Appropriate Intermediate

Given everything above, there are scenarios where MERGE with HOLDLOCK is genuinely the right tool.

Batch Synchronization and ETL Loads

When you need to synchronize a target table with a source dataset, applying updates to existing rows, inserting new rows, and optionally deleting target rows that are no longer in the source, MERGE is well-suited. The batch nature of ETL loads means the HOLDLOCK cost is amortized across many rows rather than paid per-transaction. The WHEN NOT MATCHED BY SOURCE THEN DELETE clause is particularly clean for this use case and has no simple equivalent in separate UPDATE and INSERT statements.

-- MERGE with HOLDLOCK for ETL/batch synchronization
-- Appropriate use: bulk sync of staging table to target
-- Not appropriate use: high-frequency single-row OLTP upserts

MERGE dbo.Products WITH (HOLDLOCK) AS target
USING dbo.ProductsStage AS source
    ON target.ProductID = source.ProductID
WHEN MATCHED AND (
    target.ProductName != source.ProductName OR
    target.Price       != source.Price
) THEN
    -- Only update if something actually changed
    UPDATE SET
        target.ProductName   = source.ProductName,
        target.Price         = source.Price,
        target.LastModified  = SYSDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, Price, LastModified)
    VALUES (source.ProductID, source.ProductName, source.Price, SYSDATETIME())
WHEN NOT MATCHED BY SOURCE THEN
    -- Remove target rows not in the source (use carefully)
    DELETE
OUTPUT
    $action                 AS MergeAction,
    inserted.ProductID,
    inserted.ProductName    AS NewProductName,
    deleted.ProductName     AS OldProductName
INTO dbo.ProductAuditLog (MergeAction, ProductID, NewProductName, OldProductName);
-- OUTPUT captures what happened for auditing

When You Are on SQL Server 2022 Current CU and Have No RCSI

Based on Hugo Kornelis’s September 2023 research, MERGE with HOLDLOCK on SQL Server 2022 with current cumulative updates applied, on a database without RCSI, is a defensible choice for upsert operations when the team understands and consistently applies the HOLDLOCK requirement.

11 Quick Decision Guide Beginner

Scenario	Recommendation
Single-row upsert, high concurrency OLTP, RCSI enabled	Use update-first or insert-first explicit pattern with TRY/CATCH. Avoid MERGE.
Single-row upsert, moderate concurrency, no RCSI, SQL 2022 current CU	MERGE with HOLDLOCK is defensible. Explicit patterns are still simpler and safer.
Batch synchronization / ETL staging load, infrequent concurrent access	MERGE with HOLDLOCK is appropriate. Use the OUTPUT clause for auditing.
Legacy MERGE without HOLDLOCK found in production code	Add HOLDLOCK immediately or replace with explicit patterns. This is a bug.
IF EXISTS then UPDATE else INSERT pattern under concurrent load	Replace with update-first or insert-first pattern. This has the same race condition as MERGE without HOLDLOCK.
SQL Server 2016 or earlier, any concurrency	Avoid MERGE. The bug list for older versions is significantly longer. Use explicit patterns.

References

• Aaron Bertrand (sqlblog.org): So, You Want to Use MERGE, eh? (the post Brent Ozar shared June 2026)
• Aaron Bertrand (MSSQLTips): Use Caution with SQL Server’s MERGE Statement
• Aaron Bertrand (SQLPerformance.com): Please Stop Using This UPSERT Anti-Pattern (September 2020)
• Michael J. Swart: What To Avoid If You Want To Use MERGE (August 2021)
• Michael J. Swart: SQL Server UPSERT Patterns and Antipatterns (July 2017)
• Hugo Kornelis (SQLServerFast.com): An Update on MERGE (September 2023)
• SQLSunday: How MERGE on Two Different Rows Can Still Deadlock You (May 2021)
• Microsoft Docs: MERGE (Transact-SQL)
• SQLYARD: SQL Server Index Tuning Guide
• SQLYARD: SQL Server Performance Tuning Complete Guide