Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    Unanswered: Execution Plan - really easy question

    I think I'm reading the execution plan correctly, but if someone could confirm, that would be great:

    for this statement:

    SELECT *
    FROM Sales.Customer
    WHERE
    (1=2 AND CustomerID=1)

    ...there would in fact be no filtering going on because once it's determined 1 does not equal 2 (on the left side of the AND) the right side of the AND would not bother being executed. Correct?

    thanks!
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    first that is not what a execution plan is.

    the query will never return any rows because one of the where conditions is never true.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He wants to know whether the engine will go ahead an verify the second criteria if the result set is already invalidated by the first.
    I don't think it will.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2005
    Posts
    161
    Although you will never get any rows, there is no guarantee that the where condition will be evaluated in the order that it is written. Customerid=1 may well get evaluated.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, it will not bother checking the CustomerID, and as a matter of fact the order of the predicates makes no difference. It will not check the CustomerID for this clause either:
    WHERE (CustomerID=1 AND 1=2)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    Yeah:
    (sqlserver2000)

    select * from table1 where mydate = 'not' and 1=2

    does not return an error, whereas

    select * from table1 where mydate = 'not'

    does. There's no difference in the order too.

  7. #7
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    Quote Originally Posted by blindman
    No, it will not bother checking the CustomerID, and as a matter of fact the order of the predicates makes no difference. It will not check the CustomerID for this clause either:
    WHERE (CustomerID=1 AND 1=2)

    interesting the order doesn't make a difference, but so long as it doesn't hit that column if one side of the predicate isn't true....that's what I was hoping for.

    great answer. thanks.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The reason is because constants are evaluated first regardless of their order in any clause.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...which means if neither criteria is a constant, (they reference table data instead), then both may be evaluated.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •