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.

Solarwinds

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 (https://blog.sqlauthority.com)

Solarwinds
You can download the poster from Itzik Ben-Gan’s T-SQL Querying page over here.
,
Previous Post
SQL SERVER – Queries Waiting for Memory Allocation to Execute
Next Post
SQLAuthority News – Accessing SQL Server Databases with PHP

Related Posts

5 Comments. Leave new

  • Nice poster.

    Reply
  • Partha Dutta Gupta
    July 10, 2011 2:22 am

    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.

    Reply
  • In Itzik’s book, he lists Order by before Top, as #10 and #11, respectively.

    Reply
  • hi
    good day,
    i cant find any poster on your given link it says not found.

    Reply
  • 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

    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

    Reply

Leave a Reply

Menu