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

6 thoughts on “SQL SERVER – Download Logical Query Processing Poster

  1. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 5 of 31 Journey to SQLAuthority

  2. Nice Pinal.

    Just Have a look at the following:

    CREATE TABLE AA(PRODCODE CHAR(5),
    RATE DECIMAL(20,2) NOT NULL DEFAULT 0,
    LAST_REC CHAR(1) NOT NULL DEFAULT ”)

    INSERT AA VALUES(‘P1′,25,’L’)
    INSERT AA VALUES(‘P1′,0,”)

    SELECT * FROM AA

    CREATE TABLE BB(PRODCODE CHAR(5),
    RATE DECIMAL(20,2) NOT NULL DEFAULT 0)

    INSERT BB VALUES(‘P1′,0)
    INSERT BB VALUES(‘P2′,0)

    UPDATE BB SET RATE=
    ISNULL((SELECT TOP 1 1/RATE FROM AA WHERE AA.PRODCODE=BB.PRODCODE),0)
    — It says (2 row(s) affected)
    — If TOP would not have executed first then we naturally get
    — a divide by zero error as in the next command
    UPDATE BB SET RATE=
    ISNULL((SELECT TOP 1 1/RATE FROM AA WHERE AA.PRODCODE=BB.PRODCODE ORDER BY RATE),0)
    — Msg 8134, Level 16, State 1, Line 1
    –Divide by zero error encountered.
    –The statement has been terminated.

    — Thus we may also conclude that the evaluation of Expressions and Column values in select statement is also restricted on TOP. Only TOP % or Number rows –column are retrieved or expressions are evaluated. Executing Order BY first than TOP would naturally lead to a divide by zero eror.

  3. His poster link, publicly available, is below. Note there is a valid link at the top of the article but I cannot find the poster on the 2008 Query details link page so hack it.

    To get this poster, or any failing link you want to invest in getting, try this … take the failed URL trail “Logical-Query-Processing-Poster”
    and internet search it for any other missing link yields you the below PDF

    http://www.sql.co.il/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

    I have a question on how the index order apears to be different than the logical processing order – but I need to read more on that before I ask.

    Great information as always Itzik

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s