🗄️ 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.
