🗄️ 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 BYinsideOVER()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 inORDER BYwere 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.
