Paul White NZ (5/7/2010)
Hugo Kornelis (5/7/2010)
It's not my habit to disagree with Itzik, but in this case I have to make an exception.Me either 😉 However, I think there is a subtlety here that makes both interpretations correct, but Itzik's more so:
The last two items on Itzik's list are TOP then Presentation ORDER BY. The inference here is that presentation ORDER BY is separate from TOP. My take is that TOP includes an implicit ORDER BY, and the presentation ORDER BY logically comes after that.
Ahhh, even more disagreement. Nice! Good training for the grey cells. 🙂
First: My assumption so far was that this whole discussion is about queries without any subqueries, in any of the clauses (and that includes "hidden" subqueries caused by CTE's, views, inline table-valued functions, or the APPLY operator). I think we all agree that the parentheses around subqueries imply what they imply in mathematics: that (logically) the subqeries are evaluated first. I always think of it as a virtual temporary table that is created and populated by evaluating the saubquery, and then the subquery in the original query is replaced by this virtual temporarty table. Again, all logical - this is where the actual evaluation order usually is hugely different from the logical order.
If we disagree on this assumption, if you take this discussion to be about all queries including subqueries at any nesting level, than I am prepared to agree with you on the order of the ORDER BY and TOP clauses, but I'll have to start disagreeing on almost everything else 😛
Second: Given that we now agree that this discussion is about queries with no subqueries, there is in the SQL Sever syntax no way to specify a presentation ORDER BY that differs from the ORDER BY for the TOP. There is one ORDER BY, and it always applies to both the presentation order and the TOP clause.
Third: rVadim has managed to find what I could not - the official word from Microsoft on the logical order of evaluation, as included in Books Online. In that list, ORDER BY goes before TOP.
The important distinction is that SQL Server does guarantee which rows will be qualified by TOP, given its associated ORDER BY (if any) but it does not guarantee that the same order will be reflected in the final output, even if the TOP is the last iterator in the query plan. In general, the order used to qualify rows for a TOP iterator does not say anything about the order of rows presented to the next stage of the operation.
Where in the execution plan an operator stands is indeed totally irrelevant for what Microsoft does or does not guarantee. Where in a query a clause stands is not. And Microsoft does guarantee returning results in the order of the ORDER BY of the outermost query.
So in any subquery, ORDER BY can only apply to TOP, not to presentation order, in those cases the logical evaluation order ends with ORDER BY (for the TOP only), then TOP.
In the outer query, (either a query with no subqueries, or a query with subqueries, after logically replacing all subqueries with virtual temporary tables), ORDER BY applies to both TOP and presentation order; the logical evaluation order still ends with ORDER BY (for both TOP and presentation), then TOP (which doesn't affect row ordering).
There is no guarantee of the 'row qualification' sort order being preserved. For anyone not prepared to take my word for it (and that should be all of you) please see Conor Cunningham's blog entry on the subject. If I were feeling mischievous, I might mention 'defensive programming' at this point 😛
In that very blog entry, Conor also wriites: "You’d need to put an ORDER BY at the top of the query to guarantee the output order returned to the client". In other words, presentation order is guaranteed if the ORDER BY is on the outermost query. And Conor writes: "If an ORDER BY is used in the same scope, it qualifies rows based on the ORDER BY" - so a TOP on the same outermost query will use the ORDER BY that also guarantees presentation order.
I would say that both are correct, but the question is less so - since it is missing the final presentation ORDER BY step (assuming that the ORDER BY in the question is the one associated with the TOP).
FROM
Cartesian Product
ON clause filter
Add Outer Rows
WHERE
GROUP BY
HAVING
SELECT
Evaluate expressions
DISTINCT
TOP (including qualification ORDER BY if present)
Presentation ORDER BY
The presentation ORDER BY is only relevant for the outermost scope, and if a TOP applies to that scope, it can only use the same ORDER BY. So the ORDER BY after the TOP does nothing.