Short-circuit evaluation in SQL
I did some limited hallway testing today and discovered that a fair number of people do not know that SQL Server does short-circuit evaluation. For those who don’t know, the simple explanation is that SQL Server will evaluate the left hand side of a conditional statement with an AND in the middle and if it is false the SQL will not bother with the right.
To demonstrate, try this:
select ‘short circuit’ where 1=0 and 1/0 = 0
If SQL supports short circuit evaluation nothing would be returned – obviously 1 is never equal to 0 
If SQL evaluated both sides, this would cause an error.
The implications of this on search procedures is simple yet massive. If a where clause has to check a value or ignore the check if the parameter is null, most people write this as
SELECT
Fields
FROM
Table
WHERE
table.field = @parameter OR @parameter IS NULL
If there are enough parameters, this will quickly lead to performance problems. SQL will do the left comparison regardless of the value of the parameter for every parameter, for every row in the table. Many people will resort to dynamic SQL queries to work around this. Here’s another take: Use short-circuit evaluation:
SELECT
Fields
FROM
Table
WHERE
NOT (@parameter IS NOT NULL AND table.field <> @parameter )
The left hand side will fail before the right hand side is even evaluated, and because the left hand side can be determined before the query runs, SQL is smart enough to evaluateit only once. The performance difference is worth investigating.
Why I am not able to do short ciruit evaluation here……
SQL Statement:
SELECT orgname FROM OrgView WHERE QID = 94 AND CONVERT(DATETIME,Answer) = ’2006-07-06′
The OrgView contains ORGNAME and ANSWER field both string data type and QID with INT…
Contains data….
ANSWER field contains number 1 (one) value which is not convertible to date but the QID is NOT EQUAL 94
I am expecting to do execute the first condition (QID=94) then the CONVERT(…)
So to filter all records with 94 as QID then convert the Answer fields which contains convertible date string…
BUT i got an error
“Conversion failed when converting datetime from character string.”
This is because the answer field contains value that is not convertible to datetime even the QID is NOT EQUAL 24
BELOW is sample data
======================================================================
ORGNAME QID ANSWER
======================================================================
org1 120 1
org2 94 2006-06-06
org3 98 all about loving you
======================================================================
there you go if i execute the sql above i got an error
but hey look I got the first condition to search for QID is 94 and Answer with convertible data….
thanks for the reply in advance………………
I tried the following to get a quick rule-of-thumb:
if (1=1) or (1/0 = 0)
print ‘true’
else
print ‘false’
Returned “true”
if (1=0) or (1/0 = 0)
print ‘true’
else
print ‘false’
Threw an error: “Divide by zero error encountered.”
if (1=0) and (1/0 = 0)
print ‘true’
else
print ‘false’
Returned false
And lastly,
if (1=1) and (1/0 = 0)
print ‘true’
else
print ‘false’
threw an error “Divide by zero error encountered.”
This is a quick test that shows the short-circuit evaluation works. So without looking at your query I would deduce that the left hand side of the and is returning a true.
Bizarrely enough, I tested your query and it worked.
Here’s my test script:
create table OrgView (ORGNAME varchar(100), QID int, ANSWER varchar(100))
insert into orgview (orgname, qid, answer) values(‘org1′, 120, ’1′)
insert into orgview (orgname, qid, answer) values(‘org2′, 94, ’2006-06-06′)
insert into orgview (orgname, qid, answer) values(‘org3′, 98, ‘all about loving you’)
SELECT orgname FROM OrgView
WHERE QID = 94 AND CONVERT(DATETIME,Answer) = ’2006-06-06′
Excellent article, thanks. A question:
“The implications of this on search procedures is simple yet massive. If a where clause has to check a value or ignore the check if the parameter is null, most people write this as
SELECT
Fields
FROM
Table
WHERE
table.field = @parameter OR @parameter IS NULL
If there are enough parameters, this will quickly lead to performance problems. SQL will do the left comparison regardless of the value of the parameter for every parameter, for every row in the table. Many people will resort to dynamic SQL queries to work around this. Here’s another take: Use short-circuit evaluation:
SELECT
Fields
FROM
Table
WHERE
NOT (@parameter IS NOT NULL AND table.field @parameter )
”
Unless I’m missing something, you would get the same good short-circuit evaluation effect by reversing the parts of the OR in the first example? i.e.
WHERE
@parameter IS NULL OR table.field = @parameter
it’s been two years since I wrote this, I had to come back and try figure out what I was talking about
I’m not sure what I was doing when I lifted the example, but what you say above is correct – the important thing is the @parameter is null is on the left, not the wierd double negative 
Mark
Still coming up first for ‘short circuit evaluation sql’ on google!
Unfortunately these statements are not entirely accurate. In slightly more complicated scenarios you are unable to short circuit the evaluation of a clause using boolean logic. I’m only pointing this out here becuase this page is the top result on a google search for short circuit evaluation.
You have shown that when using literals, short circuiting does happen. However, when dealing with sets of data you will find that your tests fail. For example, run the following:
CREATE TABLE ShortCircuit (
ID int )
GO
INSERT INTO ShortCircuit
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6
GO
DECLARE @ID int
SET @ID = NULL
SELECT ID
FROM ShortCircuit
WHERE @ID IS NULL OR ID = @ID
DROP TABLE ShortCircuit
Following your logic, the clause would see that @ID is indeed null and short circuit. However, the following shows this to be untrue:
CREATE TABLE ShortCircuit (
ID int )
GO
INSERT INTO ShortCircuit
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6
GO
DECLARE @ID int
SET @ID = NULL
SELECT ID
FROM ShortCircuit
WHERE @ID IS NULL OR ID = 1/0
DROP TABLE ShortCircuit
The divide by zero error is thrown showing that the statement did not short circuit. The reason why can be shown in this example:
CREATE TABLE ShortCircuit (
ID int )
GO
INSERT INTO ShortCircuit
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6
GO
DECLARE @ID int
SET @ID = 1
SELECT ID
FROM ShortCircuit
WHERE ID = @ID OR ID IN (2, 3)
DROP TABLE ShortCircuit
If the evaluation were to short after the initial true, then only 1 row would be returned, neglecting the other 2 rows we also desire. So while short circuiting may work with the literals shown above it does not in fact work in practice for querying. This can have a large impact on query times if an unknowing developer is assuming that boolean logic will short circuit; especially when using nested queries such as the following:
SELECT *
FROM TableA
WHERE (@ID IS NULL OR ID = @ID)
AND (@FK1 IS NULL OR FK1 IN (
SELECT ID
FROM TableB )
AND (@FK2 IS NULL OR FK2 IN (
SELECT ID
FROM TableC )
This may seem like a great way to ignore null parameters, but it can dramatically impact the performance of a query; going from milliseconds to several seconds or more.
For true short circuit evaluation, the only method I am aware of is the use of the CASE function.
Below doesn’t work. shouldn’t the short circuit evaluation work?
SELECT orgname FROM OrgView
WHERE isdate(Answer) = 1 and CONVERT(DATETIME,Answer) = ’2006-06-06′
I am getting conversion error here.
Is it possible that the IsNull statement is not doing short-circuiting?
For example, consider I have these statements:
DECLARE @test INT
SET @test = 1
SET @test = ISNULL(@test, (SELECT dbo.UF_errorDoneWithErrors(NULL,NULL)))
SELECT @test
SET @test = NULL
SET @test = ISNULL(@test, (SELECT dbo.UF_errorDoneWithErrors(NULL,NULL)))
SELECT @test
When I debug, In both cases the debugger steps into the function.
@sc0rp10n – I’d suggest that nested queries would be a less effective way of doing what you’re sketching out and that sql would not short-circuit because it evaluates from the innermost queries outwards…
I just read a blog post that gives a good example of when and why short circuit evaluation will fail.
http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/
It shows that based on the type of data, number of rows in the table and configuration of non-clustered indexes can cause the query optimizer to evaluate the right side of an expression first in some cases. You must use CASE statements (even though they are not ideal) to garuntee correct (and ordered) evaluation of conditions.
Jeff’s already posted the counter. But I was working up my own as well using your sample test.
create table OrgView (ORGNAME varchar(100), QID int, ANSWER varchar(100))
insert into orgview (orgname, qid, answer) values(‘org1′, 120, ’1′)
insert into orgview (orgname, qid, answer) values(‘org2′, 94, ’2006-06-06′)
insert into orgview (orgname, qid, answer) values(‘org3′, 98, ‘all about loving you’)
SELECT orgname FROM OrgView
WHERE ANSWER IS NOT NULL AND qid/0 = 1
The point is that yes, SQL Server will perform short circuit evaluation.
But NO, it is not left to right.
It is determined by the query optimizer based on least cost.
In my revised form above, IS NOT NULL on a text field is more expensive than integer division, so SQL Server zooms in on that one first. Whoalla, bang – divide by zero.
QED
I don’t think SQL Server 2008R2 uses logical short circuiting with CASE statements.
Example:
SELECT
CASE WHEN 1 = 0 THEN 1
ELSE SUBSTRING(‘ABC’,0,-1)
END
Error: Invalid length parameter passed to the substring function.
Why the heck is SQL Server trying to execute substring in this case is beyond me…
Woops! I meant to write:
SELECT
CASE WHEN 1 = 1 THEN 1
ELSE SUBSTRING(‘ABC’,0,-1)
END
The result is the same