none
column selection during Query plan processing phases in sql server 2008

    Question

  • I have read a book about query plan processing phases and find following flow of logical query execution

    1-From

    2-where

    3-Group By

    4- Having

    5- Select

    ......................

    Now my question is 

    as from clause operate first and SQL server engine generate a virtual table processing from left to right (What are the possible columns of this virtual table). If we have 2 tables with 10 and 15 columns, is there 15 column after planning first step in VT1?


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Friday, July 06, 2012 8:27 AM

Answers

  • Note that the flow you posted is the logical query flow. This describes how a SQL SELECT statement work.

    But that is not how it is physically executed, that would be deadfully ineffective. You can see the devidence for ths yourself by looking at the execution plan for a query.

    So, logically, the output from step 1 would be a "virtual table" having 25 column and all rows.

    Physically, SQL Server tries to push the WHERE clause early (ideally before the JOIN, for instance) to eliminate rows as early as possible, and it typically also only retreives the columns (again, early), that are needed for the further processing. As for details, it depends on the query, what indexes you have, statistics etc.


    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, July 06, 2012 2:46 PM
  • I would call that the "physical" query processing, not logical. Also, that link is from Sybase, and the products have been diverting since early nineties.

    Here is physical query execution for MS SQL Server: http://msdn.microsoft.com/en-us/library/ms190623(SQL.105).aspx


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Shamas Saeed Tuesday, July 10, 2012 6:09 AM
    Monday, July 09, 2012 12:15 PM

All replies

  • Shamas,

    I've studied the logical query processing flow as well and think that VT1 will include all columns until you get to the SELECT section of the flow. It is at the SELECT section that the column list is narrowed to the specified columns in your SELECT list.

    Thanks,


    Francis, MCTS: BI 2008, MCITP: BI 2008 Stay Thirsty My Friends

    Friday, July 06, 2012 2:00 PM
  • Note that the flow you posted is the logical query flow. This describes how a SQL SELECT statement work.

    But that is not how it is physically executed, that would be deadfully ineffective. You can see the devidence for ths yourself by looking at the execution plan for a query.

    So, logically, the output from step 1 would be a "virtual table" having 25 column and all rows.

    Physically, SQL Server tries to push the WHERE clause early (ideally before the JOIN, for instance) to eliminate rows as early as possible, and it typically also only retreives the columns (again, early), that are needed for the further processing. As for details, it depends on the query, what indexes you have, statistics etc.


    Tibor Karaszi, SQL Server MVP | web | blog

    Friday, July 06, 2012 2:46 PM
  • Thanks very much for your answer

    TiborK

     

    this is exactly what i am looking for. Can you please share some useful link or msdn post about physical execution order.

    Actually I got this question in an interview that's why i am curious about it.

    Thanks again.


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com


    Monday, July 09, 2012 8:20 AM
  • I got another logical query processing link

    http://dcx.sybase.com/1101/en/dbusage_en11/queryopt-b-3197621.html


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Monday, July 09, 2012 8:22 AM
  • I would call that the "physical" query processing, not logical. Also, that link is from Sybase, and the products have been diverting since early nineties.

    Here is physical query execution for MS SQL Server: http://msdn.microsoft.com/en-us/library/ms190623(SQL.105).aspx


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Shamas Saeed Tuesday, July 10, 2012 6:09 AM
    Monday, July 09, 2012 12:15 PM
  • Thanks, i will share it for sure....

    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Tuesday, July 10, 2012 6:09 AM