• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 897
  • Last Modified:

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

0
philipjonathan
Asked:
philipjonathan
  • 3
  • 2
2 Solutions
 
Alpha AuCommented:
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
 
Alpha AuCommented:
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
 
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now