Short–Circuit evaluation in Sql Server


The Short-Circuit(SC) or The minimal evaluation is the way programing languages evaluates arguments involved in Boolean expressions.
SC means that the arguments are evaluated from left to right until the value of the expression is determined. The rest of the arguments (if any left) will not be evaluated.
For example, c# evaluates the arguments (if logical operators && and || are used) from left to right.The evaluation of the expression stops as soon as the outcome is determined.

With SqlServer(and RDBMS systems in general) things are different when it comes to SC. In short, in Sql Server we can not relay on the minimal evaluation. Essentially, this is because of declarative nature of TSQL language. In TSQL we almost always describe only WHAT to do (e.g Give me the list of all orders processed on a particular day from the specific geographic area made by Customers who..etc) but not HOW to do it (Query optimiser-QO and other Sql Server engine components) will find the optimal way to fulfill your request.
Therefore, the order of evaluation of the query predicates may be sequential, from left to right, but it’s not guaranteed. QO may change the ordering and/or push the predicate evaluation down the query tree e.g to the data access operator (e.g index seek).
The following example shows a few case scenarios of how Sql Server evaluates predicates(logical expressions).

Create a test table:

Test 1: Conjunctive predicates (multiple predicates separated by AND Logical operators) and predicate evaluation sequence.

The query executes with no error.

Test1Plan
Figure 1: Conjunctive predicates – second expression evaluated first

Switching the predicate places will have no effect to the QO’s reasoning.
If we ran the same query without the second expression the query would fail due to conversion error.

Successful query: Query optimiser knows that the second expression, CurrencyId = N’HA!’ includes Primary Key constraint on the column(enforced by an unique clustered index) and therefore can evaluate to true maximum once. QO decides to “push” second predicate (CurrencyId = N’HA!’e) down to the query tree to the Index Seek data access operator making it “Seek Predicate”.

If the Seek operator does not find clustered N’HA!’ the WHERE filter evaluates to false making the first expression redundant. The first expression is shown in the Predicate section of the execution plan only for consistency purpose.

On the other hand , if the operator finds the row, the first predicate will be evaluated (Name=98765). QO will then try to convert the column value(N’98765′) to INT and than to compare with the given argument(98765) . If the conversion succeed(like in the example) the query will execute with no errors. However, if the conversion fails, the query will return error

Test 1 shows that we cannot know which expression will be evaluated first(if evaluated at all) and we must not relay on the minimal evaluation.

Test 2: Disjunctive predicates (multiple predicates separated by OR Logical operators) and predicate evaluation sequence).

The query below will execute with no conversion errors.

The successful execution of the query may lead us to believe that Sql Server uses minimal evaluation, but it’s not the case.

If the predicates switch the places, the query will still run with no errors. This wouldn’t be possible if Name=98765 predicate evaluated first, due to conversion error. The answer lies, again, in the way QO handles the query. In this case, during the optimisation, QO simplifies the query by excluding all predicates knowing that A=A will always evaluate to true for all rows. It simply uses table scan operator to return all rows.

Test1PlanORFigure 2: Disjunctive predicates, query simplification and SC

Test2 again shows that we the sequence of predicate evaluation is controlled by QO and is not short-circuited.

Sidenote:  the query below will fail due to conversion error (the order of the predicates is irrelevant). The QO cannot use clustered index seek because of the OR operator.  Even if we had an index on Name column, the index wouldn’t be used because of the implicit conversion on the column.

Conclusion:

It is not recommended to relay on the minimal evaluation in Sql Server (I would say in any DB engine) because of declarative nature of SQL language. Query Optimiser,  one of Sql Server’s relational engine components, will transform/simplify the query and during the process may reorganise the “WHERE” predicates making the order of the logical expressions irrelevant.

Thanks for reading.

Dean Mincic

Leave a Reply

Your email address will not be published.