none
Does SQL Server do short-circuit evaluation for "where" clauses?

    Question

  • Hi all,

    I'm not sure if this is the correct forum for this, but I was wondering whether or not SQL Server does short-circuit evaluation for "where" clauses. I am currently using SQL Server 2000 Standard, SP4 by the way.

    The reason I ask is that, if it does do short-circuit evaluation, I'd expect that the ordering of conditions in the "where" clause would matter for performance - I'd assume that you'd want to put the conditions in order of most to least selective so as few conditions are evaluated as possible.

    Thanks,
    EthanS
    Friday, August 22, 2008 2:32 PM

Answers

  • If you mean 'short-circuit' as in ignore some conditions if others are true, the answer is no.

     

    In order to fully utilized indexing, the filtering criteria 'should' be properly constructed. For example, there could be a significant difference between something like:

     

       WHERE Street = 'Elm St' AND AddrNbr = '1234'

    and

       WHERE AddrNbr = '1234' AND Street = 'Elm St'

     

    As you indicated, filtering is most efficient to apply the most selective first.

    Friday, August 22, 2008 3:04 PM
    Moderator

All replies

  • If you mean 'short-circuit' as in ignore some conditions if others are true, the answer is no.

     

    In order to fully utilized indexing, the filtering criteria 'should' be properly constructed. For example, there could be a significant difference between something like:

     

       WHERE Street = 'Elm St' AND AddrNbr = '1234'

    and

       WHERE AddrNbr = '1234' AND Street = 'Elm St'

     

    As you indicated, filtering is most efficient to apply the most selective first.

    Friday, August 22, 2008 3:04 PM
    Moderator
  • [One] could say this is a meaningless question.

     

    SQL is non-procedural (even a given statement within a procedural T-SQL batch is non-procedural), so there can be no difference in net effect between the "SQL short-circuits" case and the "SQL does not short circuit" case.

     

     

    John

     

    Friday, August 22, 2008 3:22 PM
  • Actually the answer is that SQL Server sometimes does short circuiting, and sometimes does not. SQL Server will generate several alternative execution plans (as mentioned in one of the posts above). Some of these execution plans might utilize short-circuiting logic. The execution plan chosen by SQL Server will depend on a variety of factors, including the selectivity of the columns in the WHERE clause, the size of the table, available indexes and more.

    SQL Server will generally choose an execution plan that works reasonably well as long as your query is well written and can take advantage of available indexes. Generally, I do not expect it to be useful from a performance stand-point to reorder the conditions in a WHERE clause.

    However, you should be on the watch out for dangerous SQL, such as the following:

    SELECT id, result
    FROM myTable
    WHERE ISNUMERIC(result) = 1
       AND CAST(result AS INT) > 5

    In cases like this, the query may sometimes work, and may sometimes throw an error. The following article at SQL Server Magazine by Itzik Ben-Gan provides a much more detailed analysis of the issue:

    http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html


    Hope this helps,
    Dennis

    Thursday, November 20, 2008 12:42 AM