none
sequence of execution of select statement

    Question

  •  

    What is the sequence of execution of select statement?

    Cosider the below query,

    SELECT [Columns..],[COUNT(*)] FROM [Table1]
    INNER JOIN [Table2] ON [Table1.'Column1'] = [Table1.Column1]
    WHERE 'Condition 1'
    AND 'Condition 2'
    AND 'Condition 3'
    AND 'Condition 4
    AND 'Condition 5
    AND 'Condition 6'
    AND 'Condition 7'

    GROUP BY [Columns..] HAVING [COUNT(*)] > 1

    In the query above, what is the sequence of execution of select statement? ALSO,LET ME KNOW IN WHICH ORDER THE "WHERE CONDITION" EXECUTES [whether it is from 'Condition 1' through 'Condition 7' ?]

    Tuesday, October 07, 2008 12:55 PM

Answers

  • The optimizer could change the order of execution, but most of the time is like this.

     

    - from / join

    - where

    - group by

    - having

    - columns list in the select clause

    - distinct

    - order by

    - top operator

     

    There is no order of evaluation for the expressions in the filter predicate.

     

     

    AMB

    Tuesday, October 07, 2008 1:26 PM
    Moderator

All replies

  • You can know it from the query execution plan .Rightmost part of the execution plan is executed first.

    Tuesday, October 07, 2008 1:02 PM
  • The optimizer could change the order of execution, but most of the time is like this.

     

    - from / join

    - where

    - group by

    - having

    - columns list in the select clause

    - distinct

    - order by

    - top operator

     

    There is no order of evaluation for the expressions in the filter predicate.

     

     

    AMB

    Tuesday, October 07, 2008 1:26 PM
    Moderator
  • Hi,

     

    Thanks for your reply.

     

    In the select statement, i just want to know, the order in which i write the WHERE CONDITIONS affect my performance (Say if i write Condition7 first and Condition1 at last, affects the performance)? If so, what is the order of execution of where conditions?

     

    Tuesday, October 07, 2008 1:31 PM
  •  

    Most times it does not matter which condition you write first .Optimiser will reorder it for the best performace.
    Tuesday, October 07, 2008 1:34 PM
  •  

    SQL server Optimizer is clever enough to decide the best execution and does not get effected by the order of boolean predicated be it in the where clause or somewhere else.
    Tuesday, October 07, 2008 1:41 PM
  •  RM123 wrote:

    Hi,

     

    Thanks for your reply.

     

    In the select statement, i just want to know, the order in which i write the WHERE CONDITIONS affect my performance (Say if i write Condition7 first and Condition1 at last, affects the performance)? If so, what is the order of execution of where conditions?

     

     

    The optimizer needs to evaluate all expressions, no matter the order, to have enough information for selecting the indexes, operators, etc. to be used in the execution plan.

     

     

    AMB

    Tuesday, October 07, 2008 1:44 PM
    Moderator
  •  

    hi experts...

    Just to add to the question...

    I learned some tiems back during my course of oracle there are some rules of precedence for conditions and operators.

    And they follows like this

    1. arithmatic operators

    2. Concatenation

    3. Comparison

    4. IS [not] NULL, LIKE

    5. BETWEEN

    6. NOT

    7. AND

    8. OR

     

    Do these rules apply to sql server also?

    Tuesday, October 07, 2008 3:36 PM
    Moderator
  • Yes, they do.

     

    Operator Precedence (Transact-SQL)

    http://msdn.microsoft.com/en-us/library/ms190276.aspx

     

     

    AMB

     

     

    Tuesday, October 07, 2008 3:45 PM
    Moderator
  •   Thanks

    I think there ARE some differences... In sql server logical operators AND NOT have higher precedense over BETWEEN, LIKE.

    Tuesday, October 07, 2008 3:57 PM
    Moderator