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.

..
if(object_A !=null && object_A.methodA()) == "some value"{
 //Perform some action ;
}

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:

USE tempdb
GO
IF EXISTS(SELECT 1 FROM tempdb.sys.objects 
          WHERE object_id=OBJECT_ID('TestShortCircuit'))
    DROP TABLE dbo.TestShortCircuit
GO
CREATE TABLE dbo.TestShortCircuit(
     CurrencyId      NCHAR(3) NOT NULL 
    ,Name            NVARCHAR(30) NOT NULL
    ,CONSTRAINT PK_CurrencyId PRIMARY KEY CLUSTERED(CurrencyId))
GO
INSERT INTO dbo.TestShortCircuit(CurrencyId,Name)
    SELECT tab.id, tab.name
    FROM (VALUES  (N'AUD',N'Australian Dollar')
                 ,(N'EUR',N'EURO')
                 ,(N'HA!',N'98765')
                 ,(N'USD',N'US Dollar')
                 ) tab(id,name)
GO

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

SELECT * 
FROM dbo.TestShortCircuit
WHERE  Name = 98765 AND CurrencyId = N'HA!'
--WHERE  Name = 98765
/*Msg 245, Level 16, State 1, Line 28
Conversion failed when converting the nvarchar value 'Australian Dollar' to data type int.*/

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

--this will return conversion error
SELECT * 
FROM dbo.TestShortCircuit 
WHERE  Name = 98765 AND CurrencyId = N'USD'

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.

SELECT * 
FROM dbo.TestShortCircuit 
WHERE N'A'= N'A' OR Name = 98765

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.

SELECT * 
FROM dbo.TestShortCircuit 
WHERE CurrencyId = N'HA!' OR Name = 98765

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. Required fields are marked *