2

Is the AND operator in SQL Server equivalent to && or & in C# (or other languages)?

Ie, will it check for second condition if first condition is found to be false?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Subin Jacob
  • 4,692
  • 10
  • 37
  • 69
  • Sorry, I could't find any documentation – Subin Jacob Mar 27 '14 at 06:11
  • Run an EXPLAIN on your query. It will show you how the query is parsed and executed, and what indexes are used, etc. Look up documentation on how to read an explain plan. – 000 Mar 27 '14 at 06:13
  • http://www.w3schools.com/sql/sql_and_or.asp – Anant Dabhi Mar 27 '14 at 06:14
  • possible duplicate of [OR Operator Short-circuit in SQL Server](http://stackoverflow.com/questions/11219791/or-operator-short-circuit-in-sql-server) – marc_s Mar 27 '14 at 06:15
  • 5
    **NO**, in SQL, there is **no guarantee** of the order in which boolean expressions are evaluated. **DO NOT** rely on this boolean "short circuiting" that might be OK in C# or other languages! [See this blog post by Remus Rusanu](http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/) for a great example – marc_s Mar 27 '14 at 06:15
  • 1
    "or other languages" - some languages define both [short-circuiting](http://en.wikipedia.org/wiki/Short-circuit_evaluation) and non-short-circuiting variants of the boolean operators, or only non-short-circuiting operators. You shouldn't assume that short-circuiting is universal in all other languages. – Damien_The_Unbeliever Mar 27 '14 at 07:06

1 Answers1

2

No, there is no short-circuiting in SQL. For example:

SELECT a, b, c
From T t
WHERE is_numeric(c) = 1 AND cast(c as numeric(10,2)) > 100.00

This may generate an invalid cast error where c is not numeric.

However you can force it to short-circuit by re-writing to use a CASE statement:

SELECT a, b, c
From T t
where
case
when is_numeric(c) = 0 then 0
when cast(c as numeric(10,2)) > 100.00 then 1
else 0 
end = 1

In general and with the exception of handling nulls any clause of the form

where <EXPR1> AND <EXPR2> AND <EXP3>

can be written as:

where case
when NOT <EXPR1> then 0
when NOT <EXPR2> then 0
when NOT <EXPR3> then 0
else 1 end = 1

Any expression of the form:

where <EXPR1> OR <EXPR2> OR <EXPR3>

Can be written as:

where case
when <EXPR1> then 1
when <EXPR2> then 1
when <EXPR3> then 1
else 0 end = 1
Ben
  • 34,935
  • 6
  • 74
  • 113
  • Which one will perform better? short circuited one (switch - case) or the other one? – Subin Jacob Mar 27 '14 at 10:09
  • 1
    1) Case may prevent the use of indexes, so if you have an indexed field you can query on then do that outside the case. 2) For non-indexed fields, case can be faster or slower, depending. For example SQL Server costs UDFs at zero, so if your WHERE has UDFs, you can often speed things up if you move them to the end of the CASE. But not always! With performance tuning, always ***test***. – Ben Mar 27 '14 at 10:40
  • In my sp this (http://pastie.org/8972992) predicate consumes 45% of CPU. Can I reduce it, if I use `case`.? – Subin Jacob Mar 27 '14 at 12:26
  • @SubinJacob it depends on the data and on the query. **test it**. – Ben Mar 27 '14 at 13:48