AND operation is a “short-circuit” operation.

The evaluation of the logical AND is actually quite simple. A set of checks evaluate to FALSE even if one of the checks returns FALSE. This simple rule means that the AND evaluation must be a “short-circuit” operation, i.e. as soon as a condition is encountered which determines the end result, all checks beyond that point can be ignored.

The test

The test below demonstrates this concept. What I have in the script is a simple query that returns results based on the evaluation of two conditions – one of which is bound to raise an exception.

01.USE AdventureWorks2008R2;
02.GO
03.BEGIN TRY
04.SELECT *
05.FROM HumanResources.Employee
06.WHERE (1 = 0)    --This one is false making the entire AND condition FALSE
07.AND (1/0 = 0); --This one results in an exception
08.
09.PRINT 'Execution was successful.';
10.END TRY
11.BEGIN CATCH
12.PRINT  'All conditions were evaluated resulting in an exception!'
13.CHAR(10) + 'Error Message : ' + ERROR_MESSAGE()
14.CHAR(10) + 'Error Number  : ' CAST(ERROR_NUMBER() AS VARCHAR(10));
15.END CATCH
16.GO
17.
18./*********************
19.RESULTS
20.*********************/
21./*
22.(0 row(s) affected)
23.Execution was successful.
24.*/

In this first query, the first condition itself evaluates to FALSE. Hence, there is no reason for the evaluation of the logical AND to continue. The query therefore returns no results. Had both conditions been evaluated, we would have received an exception.

01.USE AdventureWorks2008R2;
02.GO
03.BEGIN TRY
04.SELECT *
05.FROM HumanResources.Employee
06.WHERE (1 = 1)    --This one is TRUE,
07.--because of which the next condition will be evaluated
08.AND (1/0 = 0); --This one results in an exception
09.
10.PRINT 'Execution was successful.';
11.END TRY
12.BEGIN CATCH
13.PRINT  'All conditions were evaluated resulting in an exception!'
14.CHAR(10) + 'Error Message : ' + ERROR_MESSAGE()
15.CHAR(10) + 'Error Number  : ' CAST(ERROR_NUMBER() AS VARCHAR(10));
16.END CATCH
17.GO
18./*********************
19.RESULTS
20.*********************/
21./*
22.(0 row(s) affected)
23.All conditions were evaluated resulting in an exception!
24.Error Message : Divide by zero error encountered.
25.Error Number  : 8134
26.*/

Because the first condition retuned a TRUE in the query above, it returned an exception because the evaluation of the logical AND operation had to evaluate both conditions.

Conclusion

This simple test proves that the logical AND operation is a “short-circuit” operation. Therefore, the costliest condition (maybe a sub-query) in a set to be evaluated with a logical AND must be placed towards the end of the sequence to prevent un-necessary evaluations. Only if the preceding conditions are TRUE, will the more complex conditions be evaluated under this logic.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s