You can download the poster from Itzik Ben-Gan’s T-SQL Querying page over here.
Earlier this year, I had written article on SQL SERVER – Logical Query Processing Phases – Order of Statement Execution and I had asked one question to readers. I got very good response for this question. Today, I am going to discuss about one of the errata I have made there. I had displayed the Logical Query Processing order, where I had incorrectly listed the last two operations. I have listed the operations as ORDER BY first and TOP afterwards. The fact is that TOP is always executed first and ORDER BY after that.
The correct order of the Logical Query Processing Phases is as follows:
6.CUBE | ROLLUP
11. ORDER BY
This logical order is properly explained by Solid Quality Mentor and SQL Expert Itzik Ben-Gan in Logical Query Processing Phases in his book Inside Microsoft SQL Server 2008: T-SQL Querying. This book contains the poster that explains the Logical Query Processing Phases.
I had mentioned in the original post that I will give the best explanation of the subject in my blog post with due credit. Following are the excellent comments and explanation received as comments on the blog. I am listing the selected portion from the original comments.
If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.
The ORDER BY is ordering your results, it is forcing them to come back in a certain way (contrary to relational theory :) ). The TOP operator is telling SQL Server to restrict the rows returned to a certain number of rows (either explicitly or by percentage). If TOP happened first that would mean you would just be ordering by the number of rows specified in the top.
It would appear as though to do the top it has to know the Order By clause and it needs to understand there is an Order By, and then filter only the top number/percent of rows according to that.
The actual ordering for display or passing on to the next query happens after the TOP filter is applied (even though it looked at the order by).
In the case of ORDER BY and TOP, that in the absence of an ORDER BY clause, TOP will return arbitrary rows. Therefore, you can consider that ORDER BY precedes TOP when ORDER BY is specified, however, that may not be what actually happens procedurally, just what apparently happens when the results come back.
I am sure that after downloading poster and reading the comments on the original article, it will clarify why TOP operators come earlier than ORDER BY in the query execution plan.
Reference: Pinal Dave (http://blog.SQLAuthority.com)