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 an ORDER BY Inside a View or Subquery Being Silently Discarded Because It’s Only Honored on the Outermost Result Set

- 28.10.26 - ErcanOPAK

🗄️ Fix an ORDER BY Inside a View or Subquery Being Silently Discarded Because It’s Only Honored on the Outermost Result Set

SQL Server treats a result set as fundamentally unordered unless an ORDER BY is applied to the final, outermost query that actually returns results to the caller – an ORDER BY written inside a view definition, a subquery, or a CTE (without an accompanying TOP or OFFSET/FETCH that genuinely requires it) is explicitly not guaranteed to affect the final output order, and SQL Server is free to discard it entirely during query optimization since nothing in the relational model promises ordering at that level.

🔎 The Problem

CREATE VIEW dbo.RecentOrdersView AS
SELECT OrderId, OrderDate, CustomerId
FROM dbo.Orders
ORDER BY OrderDate DESC;   -- looks intentional, but SQL
                            -- Server doesn't guarantee it

SELECT * FROM dbo.RecentOrdersView;
-- Rows often come back in OrderDate DESC order in testing,
-- purely because of how the optimizer happened to execute
-- the underlying scan that day - then, after an index
-- change or a statistics update changes the chosen plan,
-- the same query returns rows in a completely different
-- order with no error and no warning that anything changed.

âś… Fix: Put ORDER BY Only on the Final Query That Actually Needs It

  • Remove the ORDER BY from the view (or subquery/CTE) definition itself, since SQL Server doesn’t guarantee it applies to the final result anyway, and add ORDER BY explicitly to every query that selects from that view and actually needs a specific order.
  • If a view genuinely needs to guarantee a specific row order for every consumer (rare, and usually a sign the ordering logic belongs somewhere else), that still has to be enforced by every query that reads from it – there’s no way to bake a guaranteed order into the view definition itself in standard SQL Server.
  • Never rely on an order that merely looks consistent across repeated test runs as proof that it’s actually guaranteed – add a genuine ORDER BY to the outermost query being tested instead of trusting that the optimizer will keep picking the same execution plan indefinitely.

🚨 Why This Is Dangerous

  • Because the unordered result can look correctly ordered for a long time purely by coincidence of how the optimizer happens to execute the plan, a report or export built on this assumption can ship and work fine for months before an unrelated change – a new index, an updated statistics job, a SQL Server version upgrade – flips the plan and the output order along with it, with nothing in that unrelated change calling out this side effect.

An ORDER BY that isn’t on the outermost query isn’t a guarantee – it’s a suggestion the optimizer is completely free to ignore the moment ignoring it is more efficient.

— Database Engineer

Related posts:

SQL “Parameter Sniffing Hell” — The REAL Fix Nobody Uses

SQL “Parameter Sniffing Hell” — The REAL Fix Nobody Uses

SQL Indexes Exist but Queries Stay Slow

SQL Indexes Exist but Queries Stay Slow

SQL Server: Fix an XML Index Query That Falls Back to a Full Table Scan Because the XQuery Expressio...

SQL Server: Fix an XML Index Query That Falls Back to a Full Table Scan Because the XQuery Expressio...

Views: 7

Post navigation

Photoshop: Fix a Layer Effect That Stops Scaling With Its Layer Because Scale Styles Was Left Unchecked in Preferences Before You Transformed It
Docker: Fix a Container Stuck Restarting in a Loop Because the Entrypoint Script Runs as PID 1 and Never Forwards SIGTERM to the Actual Application Process

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 (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (853)
  • 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 @ (608)
  • Average of all values in a column that are not zero in SQL (570)
  • 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 (977)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (853)

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