Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix a Merge Statement Double-Counting Matched Rows

- 17.11.26 - ErcanOPAK

🗄️ 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 @@ROWCOUNT immediately after the MERGE statement for an accurate total-rows-affected count instead of counting rows in the OUTPUT result set, since @@ROWCOUNT reflects 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.

— Database Engineer

Related posts:

How to Find Day Name From Date in SQL Server

How to Find Day Name From Date in SQL Server

SQL Queries Randomly Slow Down

SQL Queries Randomly Slow Down

Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql st...

Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql st...

Views: 6

Post navigation

The AI Prompt That Explains Why a Null Check Got Skipped in Review
The AI Prompt That Turns a Gym Routine Into a Printable Weekly Log

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,059)
  • Get the User Name and Domain Name from an Email Address in SQL (983)
  • How to add default value for Entity Framework migrations for DateTime and Bool (981)
  • Get the First and Last Word from a String or Sentence in SQL (861)
  • How to select distinct rows in a datatable in C# (856)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (658)
  • Add Constraint to SQL Table to ensure email contains @ (613)
  • Average of all values in a column that are not zero in SQL (571)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (549)
  • Find numbers with more than two decimal places in SQL (477)

Recent Posts

  • Visual Studio: Fix Quick Actions Hiding Behind a Stale Squiggle
  • Photoshop: Fix a Smart Filter Resetting After You Relink It
  • Ajax: Fix a Cancelled Request Still Updating Stale UI State
  • C#: Fix a Span Captured by a Lambda Throwing at Runtime
  • The AI Prompt That Converts a REST Endpoint Into a Typed SDK
  • Git: Fix a Worktree Locking a Branch Needed Somewhere Else
  • Asp.Net Core: Fix a Background Service Leaking Scoped Dependencies
  • SQL Server: Fix a Columnstore Index Fragmenting After Bulk Deletes
  • Docker: Fix a Container Losing Logs After a Restart Policy
  • Asp.Net Core: Fix Output Caching Serving a Stale Authorized Page

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,059)
  • Get the User Name and Domain Name from an Email Address in SQL (983)
  • How to add default value for Entity Framework migrations for DateTime and Bool (981)
  • Get the First and Last Word from a String or Sentence in SQL (861)
  • How to select distinct rows in a datatable in C# (856)

Recent Posts

  • Visual Studio: Fix Quick Actions Hiding Behind a Stale Squiggle
  • Photoshop: Fix a Smart Filter Resetting After You Relink It
  • Ajax: Fix a Cancelled Request Still Updating Stale UI State
  • C#: Fix a Span Captured by a Lambda Throwing at Runtime
  • The AI Prompt That Converts a REST Endpoint Into a Typed SDK

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com