MS SQL Boolean Operators

Dear experts,

I'm relatively new to MS SQL. Does MS SQL "short-circuit" boolean expression? The reason I ask this is to speed up my query, so that when 1 condition is fulfilled, the other needs not be evaluated at all. Simplified example of the query is given in snippet.

For a record whose column1 is 100, will MS SQL still evaluate column2 = 200?
In my actual query, the 2nd condition is more complex

Preferrably, if you have a reference to MS documentation that explain this, it would even be better.
Many thanks in advance!
SELECT *
FROM table1
WHERE
  column1 = 100 AND
  column2 = 200

Open in new window

LVL 18
philipjonathanAsked:
Who is Participating?
 
Alpha AuConnect With a Mentor Commented:
i wonder the resultset is different for the two query.

for the 1st query, if @IsFilterApplied = 0, it become a query of
select * from table1

but for the 2nd query, if @IsFilterApplied = 0, it become a query of
select * from table1 where 1<>1 (i.e. select nothing)


i wound suggest to use stored procedure and code like that.
declare procedure sp_test 
(
@IsFilterApplied bit
blah
blan
blah
)
as
 
 
if @IsFilterApplied = 1
begin
   select * from table1 where blah blah blah
end
else
begin
   select * from table1
end

Open in new window

0
 
Alpha AuConnect With a Mentor Commented:
This is determine by the execution plan of the SQL server, sometime will, sometime won't.

The following article at SQL Server Magazine by Itzik Ben-Gan provides a much more detailed analysis of the issue:
http://www.sqlmag.com/Articles/ArticleID/9148/pg/2/2.html
0
 
philipjonathanAuthor Commented:
So it's determined dynamically? In that case, I hope you don't mind my altering the question a bit then. I have a stored proc that looks like:

SELECT *
FROM table1
WHERE
  @IsFilterApplied = 0 OR
  (@IsFilterApplied = 1 AND column1 > @FilterThreshold)

Will it perform faster than:

SELECT *
FROM table1
WHERE
  (@IsFilterApplied = 1 AND column1 > @FilterThreshold)


Note that in Query 1 @IsFilterApplied is compared against 0 first, then compared against 1.
In Query 2, @IsFilterApplied is compared only against 1.
@IsFilterApplied is BIT, that indicate whether the @FilterThreshold is applied.
0
 
philipjonathanAuthor Commented:
I see it now that I've understood the WHERE condition wrongly.
As for the short-circuit question, I'll let the matter rest as it is decided by the execution plan.
Thanks!
0
 
philipjonathanAuthor Commented:
Thanks once again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.