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 ROW_NUMBER Ties Reordering Between Identical Runs

- 16.11.26 - ErcanOPAK

🗄️ SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs

ROW_NUMBER() requires an ORDER BY, but when that ordering expression has ties – multiple rows sharing the exact same value on every column actually listed – SQL Server is free to break the tie using whatever order the rows happen to arrive from the underlying query plan, which isn’t guaranteed stable across runs. A statistics update or an index rebuild can pick a different physical access path, and with it, a different but equally valid assignment of row numbers to tied rows, so pagination or deduplication logic built on the output can return a different row as “row #1 of the tie” with the query and data both completely unchanged.

🔎 The Problem

SELECT *, ROW_NUMBER() OVER (ORDER BY CustomerId) AS rn
FROM Orders;
-- many rows share the same CustomerId (the only ORDER BY column) -
-- ties among them are broken by whatever order the storage engine
-- happens to scan rows in. A stats update or index rebuild can
-- change that scan order -> a different row becomes rn = 1 for the
-- same tied group, same data, same query text.

✅ Fix: Add a Tiebreaker Column That’s Actually Unique

  • Add a tiebreaker to the ORDER BY inside OVER() that’s guaranteed unique per row – a primary key or identity column – so the partial order defined by the business columns alone always resolves to one specific, repeatable total order.
  • Don’t assume a query “already has unique output” makes ROW_NUMBER() stable; uniqueness of the final result set says nothing about whether the columns named in ORDER BY were themselves unique at the point ties get broken.
  • If reproducing exact prior numbering after a schema or index change matters, explicitly document and test against the full tiebreaker-inclusive ORDER BY, not just “the same query as before.”

🚨 Why This Is Dangerous

  • A deduplication script that keeps “row #1 of each tie” via ROW_NUMBER() without a full tiebreaker can delete a different row than intended on a re-run after an unrelated index change, with no error to flag that the “duplicate” it kept this time isn’t the one it kept last time.

ROW_NUMBER() promises an order for what you listed in ORDER BY – ties are still an open question SQL Server answers however the plan happens to visit them.

— Database Engineer

Related posts:

sp_help for SQL Server Management Studio

sp_help for SQL Server Management Studio

SQL Server: Fix New Deadlocks Appearing Only After Raising the Isolation Level Because Range Locks N...

SQL Server: Fix New Deadlocks Appearing Only After Raising the Isolation Level Because Range Locks N...

SQL: Use CTEs to Write Readable Complex Queries

SQL: Use CTEs to Write Readable Complex Queries

Views: 4

Post navigation

C#: Fix an Enum HasFlag Returning True for the Zero Value
HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

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,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (976)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (852)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (607)
  • Average of all values in a column that are not zero in SQL (569)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (546)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

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

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

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