Evaluating a Boolean Expression

When you write a boolean expression, @a = @b or @a/@b = 2, do you think the all the part of express get evaluated by SQL Server. let's have a test below:

declare @a int = 1, @b int = 0
print 'if @a = @b + 1 or @a/@b = 2'
if @a = @b + 1 or @a/@b = 2 
      print 'No Error'
print 'if @a/@b = 2 or @a = @b + 1'
if @a/@b = 2 or @a = @b + 1
      print '	Error'
go

This test illustrates that while SQL Server is evaluating a boolean expression, it seems evaluating expressions from left to right and stops evaluating the rest of them at the point where the rest of expression cannot affect the result of entire expression. This makes sense – it is important for performance –letting CPU does less is always faster than it does more.

This rule seems also applying to the boolean expression after “where” :

set nocount on
begin transaction
declare @a int = 1, @b int = 1
create table #t  (a int, b float)
insert into #t values(RAND()*100,1)
insert into #t values(RAND()*100,0)
insert into #t values(RAND()*100,3)
select * 
from #t a
where @a <> @b and a/b = 6
rollback

Is that always true in all the cases? let’s have another test:

select * 
from sys.tables
select * 
from sys.tables
where schema_id / parent_object_id >10 
	and object_id < 10

In this test, we’re supposed to get “Divide by zero error encountered.” error as we know parent_object_ids are zero, however, no error returned. this mean that expression schema_id / parent_object_id is not evaluated.

Obviously, SQL Server evaluates the expression that it can find an index for speeding up the execution based on the query plan first, then determines if the rest of work needed or not. If not, then stop processing the rest. Will that only for the expression after “where”? What about “If”? Let’s do another test:

declare @a int = 5, @b int = 5
if @a = @b or exists(select 1 from sys.tables where object_id<parent_object_id>10)
print 'Done'
go
declare @a int = 5, @b int = 5
if exists(select 1 from sys.tables where object_id<parent_object_id>10) or @a = @b
print 'Done'

The results are the same! SQL Server skips the problem SQL statement regardless its position in the entire expression. This could be an excuse to answers the question “why my application has worked well for a year but all in sudden stops working with nobody touching it”. Because data could be changed; execution plan might be changed!