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

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

Solarwinds

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

Solarwinds
, ,
Previous Post
SQL SERVER – 2008 – Management Studio New Features
Next Post
SQL SERVER – Server Type and File Extention

Related Posts

38 Comments. Leave new

  • declare @x table (i int)
    insert into @x(i) values(1),(5),(10),(2)
    select top(2) i as Z from @x
    SELECT * FROM (select top(2) i as Z from @x) y order by y.Z

    Why it applies order by before the top?:)

    Reply
  • How to use on ,out, CUBE and ROLLUP?
    please give me one example

    Reply
  • Hi Pinal,

    I would like to know what the sub query order. Please help me out about this.

    Example Query:

    Select * from Dept where DeptNo in (Select DeptNo from Emp)

    I would like know how the above query will execute.

    As of my knowledge, The above query will execute in the following Order:
    1. Main Query From Clause
    2. main Query Where Clause
    3. Sub Query From Clause
    4. Sub Query Where Clause
    5. Sub Query Select
    6. Main Query Select

    Please Let me know my thought process is correct or not.

    Thanks in advance.

    Regards,
    Sekhar Reddy

    Reply
  • CREATE TABLE #Temp (
    ID INT IDENTITY(1, 1)
    ,Ename VARCHAR(1000)
    ,Sal VARCHAR(100)
    ,DepAvgSal VARCHAR(100)
    )

    Insert into #Temp(Ename,Sal,DepAvgSal)
    select ‘Nikhil’,10,20 union
    select ‘Siva’,20,20 union
    select ‘Sandeep’,’NA’,20

    ALTER TABLE #Temp
    ADD Emsg Varchar(2000)

    –Process1:

    Update #Temp
    set Emsg=’Error’
    where isnumeric(Sal)1

    select * from #Temp
    where
    cast(Sal as float)<cast(DepAvgSal as float)
    and isnull(Emsg,'')=''

    –Process2:

    select * from #Temp
    where
    cast(Sal as float)<cast(DepAvgSal as float)
    and Emsg is null

    OR

    select * from #Temp
    where
    cast(Sal as float)<cast(DepAvgSal as float)
    and isnumeric(Sal)=1

    Drop Table #Temp

    –My Question: Why we are getting "Error converting data type varchar to float." Error in Process1 why we are not getting in process2.

    Reply
  • I am big fan of this blog

    Reply
  • I am a T SQL Newbie….but I think that the sequence depends on the output desired. – whether one wants the results to be ORDERed after selecting the TOP so many rows or maybe he wants to first ORDER them and then select the TOP so many rows….

    maybe I am wrong

    Reply
  • Can you tell the Logical Query Processing Phases order for Update,Insert,Delete Query

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

    This first line in your article contains a link to a web page that is not what the sentence claims it is. Can you update it to the correct page?

    Reply
  • Hi , This doubt may be a liitle of out of topic but i want to know what would be the order of execution if there are multiple and conditions like “where condn1 and condn2 and condn3 “? which condn is taken care first?

    Reply
  • It makes all perfect sense to order data first and then get the top 5 rows .

    Reply
  • Nikhil Kulshrestha
    December 14, 2017 2:10 pm

    If first execute the from then it mean it extract the all rows in buffer then it eliminating. Please correct me if i am wrong.

    Reply

Leave a Reply

Menu