Behavior of SQL OR and AND operator

问题:

We have following expression as T-Sql Query:

Exp1 OR Exp2

Is Exp2 evaluated when Exp1 is True? I think there is no need to evaluate it.

Similarly; for,

Exp1 AND Exp2

is Exp2 evaluated when Exp1 is false?

回答1:

Unlike in some programming languages, you cannot count on short-circuiting in T-SQL WHERE clauses. It might happen, or it might not.

回答2:

SQL Server doesn't necessarily evaluate expressions in left to right order. Evaluation order is controlled by the execution plan and the plan is chosen based on the overall estimated cost for the whole of a query. So there is no certainty that SQL will perform the kind of short circuit optimisation you are describing. That flexibility is what makes the opimiser useful. For example it could be that the second expression in each case can be evaluated more efficiently than the first (if it is indexed or subject to some constraint for example).

SQL also uses three-value logic, which means that some of the equivalence rules used in two-value logic don't apply (although that doesn't alter the specific example you describe).

回答3:

SQL Server sometimes performs boolean short circuiting, and sometimes does not.

It depends upon the query execution plan that is generated. The execution plan chosen depends on several factors, including the selectivity of the columns in the WHERE clause, table size, available indexes etc.

回答4:

SQL Server query operators OR and AND are commutative. There is no inherent order and the query optimizer is free to choose the path of least cost to begin evaluation. Once the plan is set, the other part is not evaluated if a result is pre-determined.

This knowledge allows queries like

select * from master..spt_values
where (type = 'P' or 1=@param1)
  and (1=@param2 or number < 1000)
option (recompile)

Where the pattern of evaluation is guaranteed to short circuit when @param is set to 1. This pattern is typical of optional filters. Notice that it does not matter whether the @params are tested before or after the other part.

If you are very good with SQL and know for a fact that the query is best forced down a certain plan, you can game SQL Server using CASE statements, which are always evaluated in nested order. Example below will force type='P' to always be evaluated first.

select *
from master..spt_values
where
    case when type='P' then
        case when number < 100 then 1
    end end = 1

If you don't believe order of evaluation of the last query, try this

select *
from master..spt_values
where
    case when type='P' then
        case when number < 0 then
            case when 1/0=1 then 1
    end end end = 1

Even though the constants in the expression 1/0=1 is the least cost to evaluate, it is NEVER evaluated - otherwise the query would have resulted in divide-by-zero instead of returning no rows (there are no rows in master..spt_values matching both conditions).