Solved

# MS SQL Boolean Operators

Posted on 2009-04-27
862 Views
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.
``````SELECT *

FROM table1

WHERE

column1 = 100 AND

column2 = 200
``````
0
Question by:philipjonathan

LVL 7

Assisted Solution

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

LVL 18

Author Comment

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

LVL 7

Accepted Solution

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
``````
0

LVL 18

Author Comment

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

LVL 18

Author Closing Comment

Thanks once again!
0

## Featured Post

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function