Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's examine these two statements:

IF (CONDITION 1) OR (CONDITION 2)
...

IF (CONDITION 3) AND (CONDITION 4)
...

If CONDITION 1 is TRUE, will CONDITION 2 be checked?
If CONDITION 3 is FALSE, will CONDITION 4 be checked?

What about conditions on WHERE: does the SQL Server engine optimize all conditions in a WHERE clause? Should programmers place conditions in the right order to be sure that the SQL Server optimizer resolves it in the right manner?

ADDED:

Thank to Jack for link, surprise from t-sql code:

IF  1/0 = 1 OR 1 = 1
      SELECT 'True' AS result
ELSE
      SELECT 'False' AS result


IF  1/0 = 1 AND 1 = 0
      SELECT 'True' AS result
ELSE
      SELECT 'False' AS result

There is not raise a Divide by zero exception in this case.

CONCLUSION:

If C++/C#/VB has short-circuiting why can't SQL Server have it?

To truly answer this let's take a look at how both work with conditions. C++/C#/VB all have short circuiting defined in the language specifications to speed up code execution. Why bother evaluating N OR conditions when the first one is already true or M AND conditions when the first one is already false.

We as developers have to be aware that SQL Server works differently. It is a cost based system. To get the optimal execution plan for our query the query processor has to evaluate every where condition and assign it a cost. These costs are then evaluated as a whole to form a threshold that must be lower than the defined threshold SQL Server has for a good plan. If the cost is lower than the defined threshold the plan is used, if not the whole process is repeated again with a different mix of condition costs. Cost here is either a scan or a seek or a merge join or a hash join etc... Because of this the short-circuiting as is available in C++/C#/VB simply isn't possible. You might think that forcing use of index on a column counts as short circuiting but it doesn't. It only forces the use of that index and with that shortens the list of possible execution plans. The system is still cost based.

As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.

share|improve this question
    
I'm not entirely sure, but I think the SQL standard requires that no short-circuiting takes place in expressions. However I cannot find a reference to that –  a_horse_with_no_name Sep 10 '11 at 9:56
    
Where is the final quote block from? Could you add a reference? –  Nick Chammas Jan 6 '12 at 20:28

4 Answers 4

up vote 14 down vote accepted

There's no guarantee in SQL Server if or in which order the statements will be processed in a WHERE clause. The single expression that allows statement short-circuiting is CASE-WHEN. The following is from an answer I posted on Stackoverflow:

How SQL Server short-circuits WHERE condition evaluation

It does when it feels like it, but not in the way you immediately think of.

As a developer you must be aware that SQL Server does not do short-circuiting like it is done in other programming languages and there's nothing you can do to force it to.

For further details check the first link in the above blog entry, which is leading to another blog:

Does SQL Server Short-Circuit?

The final verdict? Well, I don't really have one yet, but it is probably safe to say that the only time you can ensure a specific short-circuit is when you express multiple WHEN conditions in a CASE expression. With standard boolean expressions, the optimizer will move things around as it sees fit based on the tables, indexes and data you are querying.

share|improve this answer
    
+1 I asked about IF and in order of your answer I decided to ask other one about WHERE –  garik Sep 2 '11 at 8:23
2  
Apparently there are some edge cases (or a bug) where even case is not safe –  Jack Douglas Sep 2 '11 at 10:23
    
@Jack: Thanks for pointing this out. Looks like it has been fixed in June, so that case will work again as expected. –  MicSim Sep 2 '11 at 10:48
    
From the link: "But any SQL release in your hands right now will still be vulnerable to this problem" - is that wrong, have MS patched current versions? –  Jack Douglas Sep 2 '11 at 10:59
    
No, current versions aren't patched, AFAIK. I meant that future versions will work again how it should be. –  MicSim Sep 2 '11 at 11:12

In T-SQL, the IF statement can short-circuit, but you cannot rely on it evaluating the expressions in order

share|improve this answer
    
+1 super, thanks –  garik Sep 2 '11 at 11:51

1)--OR ( any one or both conditions will be TRUE)

if condition 1 is TRUE then condition 2 will also checked it can be either TRUE or FALSE

--AND ( both conditions must be TRUE)

if condition 1 is FALSE then condition 2 will not be checked

share|improve this answer
    
"if condition 1 is FALSE then condition 2 will not be checked" This is not true. See the answer above. SQL Server may still evaluate condition 2 because it does not perform short-circuit evaluation in WHERE clauses. –  Nick Chammas Sep 9 '11 at 23:04

The only way to control how conditions within the WHERE clause is to use brackets to group them together.

WHERE Col1 = 'Something' AND Col2 = 'Something' OR Col3 = 'Something' and Col4 = 'Something'

is very different from

WHERE (Col1 = 'Something' AND Col2 = 'Something') OR (Col3 = 'Something' and Col4 = 'Something')
share|improve this answer
    
Just curious. How are these two conditions different? Different results, performance, execution plan? I thought they would be equivalent. –  ypercube Jun 24 '12 at 21:01
    
With the first one you need to match Col1, Col4 and either Col2 or Col3. In the second line to match Col1 and Col2 or you need to match Col3 and Col4 but Col1 and Col4 will never need to both be evaluated together. –  mrdenny Jul 1 '12 at 13:16
1  
No, you are wrong. AND has higher precedence than OR. Both are equivalent. What you say would be true for the WHERE Col1 = x AND (Col2 = x OR Col3 = x) AND Col4 = x query. See SQL-Fiddle test –  ypercube Jul 1 '12 at 13:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.