SQL SERVER – Download Logical Query Processing Poster

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:

1. FROM
2. ON
3.OUTER
4.WHERE
5.GROUP BY
6.CUBE | ROLLUP
7.HAVING
8. SELECT
9. DISTINCT
10. TOP
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.

Download Logical Query Processing Poster

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.

Brian Tkatch

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.

Mike Walsh

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).

Bob

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)

You can download the poster from Itzik Ben-Gan’s T-SQL Querying page over here.
About these ads

SQL SERVER – Logical Query Processing Phases – Order of Statement Execution

You can download the poster from Itzik Ben-Gan’s T-SQL Querying page over here.

Of late, I penned down an article – SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN – which received a very intriguing comment from one of my regular blog readers Craig. According to him this phenomenon happens due to Logical Query Processing. His comment instigated a question in my mind. I have put forth this question to all my readers at the end of the article. Let me first give you an introduction to Logical Query Processing Phase.

What actually sets SQL Server apart from other programming languages is the way SQL Server processes its code. Generally, most programming languages process statement from top to bottom. By contrast, SQL Server processes them in a unique order which is known as Logical Query Processing Phase. These phases generate a series of virtual tables with each virtual table feeding into the next phase (virtual tables not viewable). These phases and their orders are given as follows:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

As OUTER join is applied subsequent to ON clause, all rows eliminated by the ON clause will still be included by the OUTER join as described in the article SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN.

However, I am perplexed about the last two, ORDER BY and TOP. According to some people TOP comes first in logical query processing phase while others suggest that ORDER BY comes first. Now, here I’ve laid down my questions for you all to think about:

1) What is the correct answer for order query processing phase – ORDER BY or TOP?
2) How can we create an example to verify query processing phase for ORDER BY and TOP?

I will soon publish the answers I receive to the above questions on this blog, with due credit given to my readers.

Reference : Pinal Dave (http://blog.SQLAuthority.com)