none
Order of Execution

    Question

  • SELECT

    FROM

    WHERE

    GROUP BY

    HAVING

    ORDER BY

    WHAT IS THE ORDER OF EXECUTION ABOVE STMTS IN SQL SERVER?

    CAN U PLZ TELL ME WHICH ONE EXECUTE FIRST,SECOND,ETC.,

    Monday, May 03, 2010 4:32 AM

Answers

All replies

  • Order of Exection:

    1. From

    2. Where

    3. Group by

    4. Having

    5. Select

    6. Order by

    Monday, May 03, 2010 4:34 AM
  • You have to take a look at the execution plan.

    ORDER BY is usually done last, but no guarantee. Demo follows.

    SET SHOWPLAN_TEXT ON
    GO
    SELECT Color, ColorCount=COUNT(*)
    FROM AdventureWorks2008.Production.Product
    WHERE ProductID > 799 and Color is not null
    GROUP BY Color
    HAVING COUNT(*) > 10
    ORDER BY ColorCount DESC
    GO
    SET SHOWPLAN_TEXT OFF
    /*
    Color	ColorCount
    Black	55
    Yellow	33
    Blue	25
    Silver	24
    */
    /*
    StmtText
     |--Sort(ORDER BY:([Expr1003] DESC))
        |--Filter(WHERE:([Expr1003]>(10)))
          |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
             |--Stream Aggregate(GROUP BY:([AdventureWorks2008].[Production].[Product].[Color]) DEFINE:([Expr1006]=Count(*)))
               |--Sort(ORDER BY:([AdventureWorks2008].[Production].[Product].[Color] ASC))
                  |--Clustered Index Seek(OBJECT:([AdventureWorks2008].[Production].[Product].[PK_Product_ProductID]), SEEK:([AdventureWorks2008].[Production].[Product].[ProductID] > (799)), WHERE:([AdventureWorks2008].[Production].[Product].[Color] IS NOT NULL) ORDERED FORWARD)
       */

    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Monday, May 03, 2010 4:35 AM
    Moderator
  • Order of Exection:

    1. From

    2. Where

    3. Group by

    4. Having

    5. Select

    6. Order by

    We would execute it that way in manual logical simulation, but the database engine follows a query plan which maybe quite different. The first step in the example following:

    CLUSTETED INDEX SEEK operation:

    SELECT Color FROM AdventureWorks2008.Production.Product
    WHERE ProductID > 799 and Color is not null

    SET SHOWPLAN_ALL ON
    GO
    SELECT Color, ColorCount=COUNT(*)
    FROM AdventureWorks2008.Production.Product
    WHERE ProductID > 799 and Color is not null
    GROUP BY Color
    HAVING COUNT(*) > 10
    ORDER BY ColorCount 
    GO
    SET SHOWPLAN_ALL OFF

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQLUSA.com
    Monday, May 03, 2010 6:18 AM
    Moderator
  • Nodoy can guarantee that in what series these statements are executed , sql server generate many execution plan at the run time dependent on data , mdop etc and then it selects best for the execution.
    Monday, May 03, 2010 12:11 PM
  • You can read Plamen's blog on this exact topic http://pratchev.blogspot.com/2007/05/anatomy-of-query.html
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, May 03, 2010 12:53 PM
    Moderator
  • Similarly Rob Farley's chapter in the mvp deep dives book is excellent. 
    Dave Ballantyne ---- http://sqlblogcasts.com/blogs/sqlandthelike/
    Monday, May 03, 2010 1:41 PM
  • Here is a nice poster about Logical Query Processing, courtesy of Itzik Ben-Gan. I strongly recommend his latest book about "T-SQL Querying".

    http://www.solidq.com/insidetsql/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

     

    AMB

    • Proposed as answer by Naomi NModerator Monday, May 03, 2010 1:56 PM
    • Marked as answer by KJian_ Friday, May 07, 2010 8:22 AM
    Monday, May 03, 2010 1:50 PM
    Moderator