The upsert procedure has been in production for two years. It is a single SQL Server MERGE statement, and every developer who reviewed it agreed that one statement cannot race against itself. Then order volume tripled and the error log filled with Msg 2627, Violation of PRIMARY KEY constraint 'PK_AccountDetails'. Cannot insert duplicate key in object 'dbo.AccountDetails'. The statement that was supposed to handle the duplicate is the one raising it.
That failure is documented, it is old, and the fix is one hint. The rest of what breaks in MERGE is a shorter list than its reputation suggests and a nastier one. Two of the three open items raise loud errors. One of them returns wrong results and raises nothing.
The MERGE behavior below applies to SQL Server 2008 through 2022 and to Azure SQL Database unless a version is named, on every edition. SQL Server 2025 went GA in November 2025 and nobody has published a re-test of the three open items against it, so every build number below is the highest the author who found that bug confirmed. The scripts use THROW (2012 and later) and CREATE OR ALTER (2016 SP1 and later).
What the statement promises
CREATE TABLE dbo.AccountDetails
(
Email nvarchar(320) NOT NULL CONSTRAINT PK_AccountDetails PRIMARY KEY,
Etc nvarchar(max) NULL
);
GO
CREATE OR ALTER PROCEDURE dbo.AccountDetails_Upsert
@Email nvarchar(320),
@Etc nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;
MERGE dbo.AccountDetails AS tgt
USING (SELECT @Email AS Email, @Etc AS Etc) AS src
ON src.Email = tgt.Email
WHEN MATCHED THEN
UPDATE SET Etc = src.Etc
WHEN NOT MATCHED THEN
INSERT (Email, Etc) VALUES (src.Email, src.Etc);
END
GO
One statement, one pass over the target, no IF EXISTS round trip. The terminating semicolon is required and error 10713 fires without it. A MERGE allows at most two WHEN MATCHED clauses, one WHEN NOT MATCHED [BY TARGET], and at most two WHEN NOT MATCHED BY SOURCE.
The concurrency problem
MERGE runs as one statement, which is not the same thing as running under an isolation level that protects the key you are about to insert. David Browne of Microsoft, on the MSDN blog: "By default MERGE will perform the 'match' phase of the query without using exclusive locking... when multiple sessions concurrently attempt to insert the same key value, you get a race condition."
The single-row source above is the shape that races hardest, because every caller arrives with one key and no batching. A nightly load has the opposite shape and its own prerequisite: get the incoming rows into a real object first, because staging and deduplicating the source rows is what stops error 8672 and gives the optimizer a row count instead of a guess.
Hugo Kornelis traced it through the plan in 2023: the Clustered Index Seek on the target "will use a shared lock that only exists for the duration of the read." Session A seeks alice@example.com, finds nothing, drops the lock. Session B seeks the same key, finds nothing. Both take the insert branch. One wins, one gets 2627.
Reproduce it with two SSMS windows against the table above. Both sessions walk the same key sequence, so wherever they land on the same key at the same moment, both read the target before either insert lands. Clear the table first, from one window only:
TRUNCATE TABLE dbo.AccountDetails;
Then paste this into two windows and start them together:
SET NOCOUNT ON;
DECLARE @i int = 0, @errors int = 0, @email nvarchar(320);
WHILE @i < 20000
BEGIN
SET @email = N'user' + CAST(@i AS nvarchar(10)) + N'@example.com';
BEGIN TRY
EXEC dbo.AccountDetails_Upsert @Email = @email, @Etc = N'x';
END TRY
BEGIN CATCH
IF ERROR_NUMBER() IN (2601, 2627)
SET @errors += 1;
ELSE
THROW;
END CATCH;
SET @i += 1;
END
SELECT duplicate_key_errors = @errors;
The window that arrives second on a key should take WHEN MATCHED. Some of the time it takes WHEN NOT MATCHED and reports 2627, because it read the target before the other session's insert landed. Run one session alone and the count is 0, which is why this defect survives testing and surfaces the first time two users register the same address in the same millisecond.
HOLDLOCK is not optional
MERGE dbo.AccountDetails WITH (HOLDLOCK) AS tgt
USING (SELECT @Email AS Email, @Etc AS Etc) AS src
ON src.Email = tgt.Email
WHEN MATCHED THEN
UPDATE SET Etc = src.Etc
WHEN NOT MATCHED THEN
INSERT (Email, Etc) VALUES (src.Email, src.Etc);
HOLDLOCK is a synonym for SERIALIZABLE, and Microsoft states the effect on the MERGE reference page: "In some scenarios where unique keys are expected to be both inserted and updated by the MERGE, specifying the HOLDLOCK will prevent against unique key violations." The target read now takes a range lock held to the end of the transaction, which for a standalone MERGE in autocommit is the end of the statement and inside BEGIN TRANSACTION is your commit. Session B blocks instead of seeking into the same gap.
Bertrand's rule is "ALWAYS use HOLDLOCK on the target." Kornelis, who spends most of his post arguing the case against MERGE is overstated, lands on the same hint: "Why not simply add the HOLDLOCK hint to the MERGE? That is an easier change that also completely eliminates the race condition here."
What HOLDLOCK costs you
Range locks cover the gap between two existing keys, not a single row. Daniel Hutmacher built a repro where two sessions insert different keys and deadlock anyway: with rows at 100 and 10000, one session inserting 500 and another inserting 1000 both take a range lock over the same gap and each waits for the other. Two inserts into one sparse region of the key deadlock each other.
Sequential surrogate keys narrow the exposure because new rows land past the last key. A sparse natural key with large gaps, an email address or an account number, widens it. Retry logic around 1205 is not optional either.
Which bugs are still open
This is the part people repeat without checking. Bertrand's link roundup at sqlblog.org/merge and Swart's 2021 follow-up point at the issues; Kornelis re-tested the list against SQL Server 2022 CU7 in September 2023 and published repro scripts with his results. Most of it did not survive, and Bertrand's page now says so.
Fixed, and stop citing it: unique filtered indexes. Paul White's 2012 repro raises Msg 2601, Cannot insert duplicate key row in object 'dbo.#Target' with unique index 'uq1' on a MERGE that only updates, because the optimizer picked a narrow per-row plan where a transient key violation was possible. His post records the fix builds: 2016 SP2-CU5 (13.0.5264.1), 2017 RTM-CU13-OD (14.0.3049.1), 2019 CTP2.2 (15.0.1200.24). Kornelis writes it as "This bug is fixed, since SQL Server 2019." Both are right: every 2019 and later build has the fix, and patched 2016 and 2017 branches carry it forward from those builds. On 2016 or 2017, check your build against White's list.
Fixed: the 2013 indexed view staleness bug. Connect 771336, "Indexed view is not updated on data changes in base table," hit 2008 SP3 through 2012 SP1 and was fixed by cumulative update per branch. 2014 and later never had it. It is not the indexed view bug below.
Gone, or gone quiet. Kornelis could not reproduce the simple-recovery assertion, the fulltext index failure, or the single-partition assertion on SQL Server 2022. "I was not able to reproduce this" means fixed or dormant on that build, not proven never to have been a defect. Two others were never MERGE defects: the CDC complaint behaves the same under a plain UPDATE, and memory-optimized tables as a target raise a documented error.
Open: an indexed view's base table. The feedback item Swart cites as "Merge statement Delete does not update indexed view in all cases" still reproduces on SQL Server 2022 CU7. Kornelis pins the condition tighter than that title does: an UPDATE and a DELETE action with no INSERT action, against a base table of an indexed view. In that shape "the optimizer fails to include the operators that are needed to maintain the data in the indexed view." He tested the neighbours and they are fine: delete only, update only, insert plus delete, and all three together each maintain the view. His verdict on the broken one: "you will get no indication that the data in your database has become inconsistent." A routine DBCC CHECKDB stays quiet too, because on indexed views "only physical consistency checks are performed by default" and the logical checks need WITH EXTENDED_LOGICAL_CHECKS. The view does not have to exist when you write the MERGE, which is what makes this one dangerous over a codebase's life.
Open: temporal tables with a nonclustered index on the history table. Erland Sommarskog's repro produces Attempting to set a non-NULL-able column's value to NULL, and Kornelis reproduced it on 2022 CU7. The plan choice flips it. With no nonclustered index on the history table the optimizer picks a narrow plan and the statement succeeds. Add the index and "the optimizer chose a wide update plan for the clustered and nonclustered indexes on the history table. And this fails." His reading of why, which he flags as unprovable "since that property is not exposed in the execution plan, not even in the XML," is that the optimizer "failed to add the same invisible filter on new rows to the (nonclustered) Index Insert that it did create on the Clustered Index Insert." His guidance: keep MERGE off a temporal table unless the history table never carries a nonclustered index.
Open: MERGE that updates through a view. Paul White published this in February 2025. A MERGE against a schemabound view containing a LEFT JOIN assumes a row exists in the joined table and tries to update a row that is not there. He reports Could not open File Control Block (FCB) for invalid file ID, sparse column binding errors, and assertions that dump stack and drop the connection. He validated it on SQL Server 2008 through 2022 CU17 inclusive, and his hedges belong in any retelling: "All Editions are expected to be vulnerable, but I have only confirmed it on Developer," and on Azure an aborted connection "strongly suggests" the platform is affected without proving it. Use UPDATE against the view, or an INSTEAD OF trigger.
The two fixed items came out of Microsoft's own pipeline and shipped at named cumulative-update builds. The three open ones rest on practitioner testing with published repro scripts, and no Microsoft advisory covers them. Their feedback items were open when each author last checked, 2023 for the first two and February 2025 for the third; nobody re-verified the portal for this article.
Halloween protection and plan shape
The common claim is that MERGE drags in extra spools. White's testing says the opposite for the upsert case. When the values in WHEN NOT MATCHED BY TARGET are an exact match for the ON clause of the USING and the target has a unique key, hole-filling applies and the plan carries no Eager Table Spool. White: "Notice the lack of an Eager Table Spool in this plan. Despite that, the query still produces the correct error message." The equivalent INSERT ... WHERE NOT EXISTS needs Halloween protection and gets the spool.
The mechanism is a Clustered Index Merge operator, which White describes as "read a row from the source table and immediately try to insert it into the target. If a key violation results, the error is suppressed, the Insert operator outputs the conflicting row it found, and that row is then processed for an update or delete operation." The plan choice that enables it is where the wrong-plan bugs live. The filtered index bug was a narrow per-row plan skipping the Split, Filter, Sort, and Collapse operators a wide plan uses to break transient key violations apart. The temporal table bug runs the other way: the index forces a wide plan and the wide plan fails. Neither shape is the safe one.
Check which plan you got before trusting it. OPTION (QUERYTRACEON 8790) forces a wide plan and was White's workaround for the filtered index case; 8790 is not in Microsoft's supported list for that hint, so keep it in a test session. White's condition on the whole optimization: "careful benchmarking is required to ensure performance is better than using separate statements."
Triggers fire once per action branch
The folklore about inserted and deleted arriving mixed together is wrong, and the real behavior is worse for a different reason. Microsoft documents half of this. The reference page gives the firing rule, "For every insert, update, or delete action specified in the MERGE statement, SQL Server fires any corresponding AFTER triggers defined on the target table," and it gives the @@ROWCOUNT behavior. It says nothing about what inserted and deleted hold per firing. That part is demonstrated, not documented. Run Bertrand's demo:
CREATE TABLE dbo.MyTable (id int);
INSERT dbo.MyTable VALUES (1), (4);
GO
CREATE TRIGGER dbo.MyTable_All ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN
PRINT 'Executing trigger. Rows affected: ' + RTRIM(@@ROWCOUNT);
IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
PRINT ' I am an insert...';
IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
PRINT ' I am an update...';
IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
PRINT ' I am a delete...';
END
GO
MERGE dbo.MyTable WITH (HOLDLOCK) AS Target
USING (VALUES (1), (2), (3)) AS Source(id)
ON Target.id = Source.id
WHEN MATCHED THEN UPDATE SET Target.id = Source.id
WHEN NOT MATCHED THEN INSERT (id) VALUES (Source.id)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
Output:
Executing trigger. Rows affected: 4
I am an insert...
Executing trigger. Rows affected: 4
I am an update...
Executing trigger. Rows affected: 4
I am a delete...
Three firings from one MERGE, one per action branch. Two action clauses fire twice; the count tracks the branches you wrote.
That trigger prints EXISTS results, so it shows each firing seeing one action and not the others without showing how many rows. Swap the body for counts:
CREATE OR ALTER TRIGGER dbo.MyTable_All ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SELECT rowcount_function = @@ROWCOUNT,
rows_in_inserted = (SELECT COUNT(*) FROM inserted),
rows_in_deleted = (SELECT COUNT(*) FROM deleted);
END
GO
Reset the table to (1), (4) and re-run the MERGE. Each firing reports the rows belonging to its own action: 2 and 0 for the insert pass, 1 and 1 for the update pass, 0 and 1 for the delete pass. Microsoft puts none of that in writing, so confirm it on your build before a trigger depends on it.
@@ROWCOUNT is the part that bites. It reads 4 in all three firings, because one row updated plus two inserted plus one deleted is four rows for the whole statement. Microsoft documents this: "The @@ROWCOUNT inside any AFTER trigger (regardless of data modification statements the trigger captures) will reflect the total number of rows affected by the MERGE." The insert pass reports two rows in inserted beside a @@ROWCOUNT of 4. Any trigger that sizes a batch, picks a code path, or writes an audit count from @@ROWCOUNT is reading a number that belongs to the statement, not to the rows it can see. Rewrite those to SELECT COUNT(*) FROM inserted.
One more rule: if the target has an enabled INSTEAD OF trigger for any action the MERGE performs, it must have one for every action the MERGE specifies.
OUTPUT and $action
$action is nvarchar(10) and returns INSERT, UPDATE, or DELETE per row. Microsoft calls OUTPUT "the recommended way to query or count rows affected by a MERGE," which matters because @@ROWCOUNT gives you one number for three operations.
Read the WHEN NOT MATCHED BY SOURCE THEN DELETE clause before running this against the table from the first block: the staging table starts empty, so as printed the statement empties dbo.AccountDetails.
CREATE TABLE dbo.AccountStaging
(
Email nvarchar(320) NOT NULL PRIMARY KEY,
Etc nvarchar(max) NULL
);
GO
DECLARE @changes TABLE
(
action_taken nvarchar(10),
old_email nvarchar(320),
new_email nvarchar(320)
);
MERGE dbo.AccountDetails WITH (HOLDLOCK) AS tgt
USING dbo.AccountStaging AS src
ON src.Email = tgt.Email
WHEN MATCHED AND tgt.Etc <> src.Etc THEN
UPDATE SET Etc = src.Etc
WHEN NOT MATCHED THEN
INSERT (Email, Etc) VALUES (src.Email, src.Etc)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action, deleted.Email, inserted.Email
INTO @changes (action_taken, old_email, new_email);
SELECT action_taken, row_count = COUNT(*)
FROM @changes
GROUP BY action_taken;
That breakdown is the thing separate statements make you assemble by hand, and it is the strongest argument for MERGE in an ETL load. It is also the only clean way to get generated keys across the three action branches back out of a MERGE: SCOPE_IDENTITY() returns one value from a statement that may have inserted four hundred rows, and OUTPUT inserted.OrderID returns all of them beside the $action that produced each one.
Performance against separate statements
Microsoft's own guidance on the MERGE page is not a recommendation to use it: "When simply updating one table based on the rows of another table, improve the performance and scalability with INSERT, UPDATE, and DELETE statements," and "At scale, MERGE might introduce complicated concurrency issues or require advanced troubleshooting."
Two shapes to watch:
WHEN NOT MATCHED BY SOURCEforces the plan to account for every row in the target, not the rows your source touched. Load 5,000 changed rows into a 40 million row dimension with that clause present and the plan reads the dimension. Those figures illustrate the shape, they are not measurements. Filter the target in theONclause or drop the clause.- Without filters on the source, Microsoft notes the statement "might perform a table scan or clustered index scan on the source table, as well as a table scan or clustered index scan of target table."
TOP does not batch a MERGE the way it batches an UPDATE: "The TOP clause applies after the entire source table and the entire target table join." The join runs first, then TOP discards.
Error 8672, The MERGE statement attempted to UPDATE or DELETE the same row more than once, fires when two source rows match one target row. Deduplicate the source before it reaches USING. A quieter one: when the source returns no rows at all, its columns cannot be read, so a WHEN NOT MATCHED BY SOURCE THEN UPDATE clause that references one fails with error 207, Invalid column name.
Splitting the work back into separate statements moves a cost rather than removing one. Three statements in one transaction need the rollback semantics, and a MERGE under HOLDLOCK needs a caller that survives 1205. Either way you are writing the error handling yourself, and rollback, XACT_ABORT, and retrying a deadlock is a longer subject than one line of SET XACT_ABORT ON suggests.
The verdict
MERGE is safe under conditions you can state in one breath. Use it when all of these hold.
- The target is not a base table of an indexed view, and nobody is going to add one later. The failing shape, an
UPDATEplus aDELETEwith noINSERT, is the one open bug that returns wrong answers and raises nothing. - The target is not a temporal table, or the history table has no nonclustered index.
- The target is a table, not a view. Paul White's 2025 repro is open through 2022 CU17.
- The statement carries
WITH (HOLDLOCK)on the target, and the caller retries on 1205. - The source is deduplicated on the join key.
- You want
$actioncounts, or the set-based insert-update-delete in one pass is what the job needs.
Reach for separate statements when the workload is a high-frequency single-row upsert from an application. Reach for neither when the nightly job replaces the whole table: emptying and reloading instead of merging skips the join, the plan choice and every bug above, and on a staging table nobody queries at 3 a.m. it is the cheaper answer. Bertrand's pattern does that job with no open bugs attached. SET XACT_ABORT ON is an addition, not his: his block is the transaction, the hinted UPDATE, and the guarded INSERT.
SET XACT_ABORT ON;
BEGIN TRANSACTION;
UPDATE dbo.AccountDetails WITH (UPDLOCK, SERIALIZABLE)
SET Etc = @Etc
WHERE Email = @Email;
IF @@ROWCOUNT = 0
INSERT dbo.AccountDetails (Email, Etc) VALUES (@Email, @Etc);
COMMIT TRANSACTION;
UPDLOCK heads off the conversion deadlock at statement level, SERIALIZABLE holds the range for the transaction. When inserts outnumber updates, flip the order: insert first with a WHERE NOT EXISTS guarded by the same hints.
Kornelis, after re-testing every complaint on the list, landed on two rules rather than a ban: "Do not use MERGE with a DELETE action. Do not use MERGE to target a temporal table." Swart, after the same exercise, landed on "Avoid: MERGE." Both read the same evidence. What separates them is a judgment about how much unattended risk a shop can carry, which is a call you make about your own schema.
Neither of them argues about the hint: a MERGE without HOLDLOCK is broken today, on every supported version, and it passes the tests you run against an idle server.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.