🗄️ SQL Server: Fix a Merge Statement Double-Counting Matched Rows
The actual row modifications a MERGE statement performs are correct even under a parallel execution plan, but Microsoft’s own documentation calls out a specific, narrower gotcha: when a parallel plan is chosen for a MERGE that also uses the OUTPUT clause, that clause can return MORE rows than were actually modified, because multiple parallel threads can each contribute a row to the output stream for what is, at the table level, a single logical change. Any application code that tallies ‘rows processed’ by counting the OUTPUT result set — for an audit log, a billing count, a progress indicator — ends up double-counting purely as an artifact of the plan shape, with the underlying data completely correct the whole time.
🔎 The Problem
MERGE INTO dbo.Accounts AS target
USING dbo.StagingUpdates AS source
ON target.AccountId = source.AccountId
WHEN MATCHED THEN
UPDATE SET Balance = source.NewBalance
OUTPUT $action, inserted.AccountId INTO dbo.AuditLog;
-- under a PARALLEL execution plan, AuditLog can end up with more
-- rows than Accounts rows actually changed - the UPDATE itself is
-- correct, but the OUTPUT-driven audit count is inflated
✅ Fix: Don’t Trust OUTPUT Row Counts From a Parallel MERGE
- Force a serial plan specifically for MERGE statements whose OUTPUT clause feeds row-counting logic, with
OPTION (MAXDOP 1), if that statement isn’t performance-critical enough to need parallelism. - Use
@@ROWCOUNTimmediately after the MERGE statement for an accurate total-rows-affected count instead of counting rows in the OUTPUT result set, since@@ROWCOUNTreflects the real row modification count regardless of plan shape. - If you must use OUTPUT for row-level detail rather than just a count, deduplicate the result set in the consuming application or in a wrapping query rather than assuming one output row always means exactly one underlying change.
🚨 Why This Is Dangerous
- An automated billing or audit process silently over-counting modified rows under load specifically because the optimizer chose a parallel plan for a MERGE it didn’t choose during low-traffic testing — the exact same statement produces a correct count in one environment and an inflated one in another.
A MERGE statement’s data is correct under parallelism — its OUTPUT row count is not guaranteed to be.
