Is the SQL WHERE clause short-circuit evaluated?

Question

For example:

SELECT *
FROM myTable
WHERE dbo.f1(myTable.col1)
  AND dbo.f2(myTable.col2)

If dbo.f1(myTable.col1) evaluates to false, does dbo.f2(myTable.col2) still get evaluated for a row?

If No, why not?

If Yes, is it guaranteed?

Your help is appreciated.

---

Microsoft Marketing should care for customer feedback on their consumer products, e.g. Windows & Office.

Answer 1

When the query optimizer determines the query plan, it may include shortcuts. There is not guarantee for shortcutting and no guarantee for order of processing.

So in your example, the optimizer may choose to execute dbo.f2() before it considers executing dbo.f1(). Or it could just as easily execute both of them all the time (shortcutting), or any other combination or order.

---

Gert-Jan

Answer 2

It will as you have AND in your query. You can verify it using a profiler.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

Answer 3

This is not possible. Functions must return a value of a data type. BOOL is not a data type.. You may use a BIT. Here is a nice article about it.

Answer 4

If you want short-cutting, you can (with ~ 90% of guarantee, not 100%) try to employ CASE based query, e.g.

where 1 = case when dbo.f1(myTable.col1) = 1 then 1 when dbo.f2(myTable.col2) = 1 then 1 else 0 end

However, even if you write your WHERE condition this way, there is a chance that f2 will still be executed.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

For sure sort circuiting happens in SQL. This example shows that

declare @table table(id int,name varchar(50))
insert into @table values
(1,'satheesh')
select * from @table
where 1=2 and 
name=1--this should give a conversion error but it is short circuited

Incase of constants it is very easy, however when for function calls it depends on the optimizer and it is not guarenteed that short circuiting happen(in case of constants it is guarenteed though)

Regards
Satheesh

Answer 6

Thanks, guys, for your replies,

so, basically, it seems there is no general answer to that question. Am I right with this assumption?

@ Stefan:

Yes, you are right. I was just making up a sample case and didn't thoroughly check the statement for correct syntax. It would have been correct to add some logical operator expression to the WHERE clause, like, e.g., dbo.f1(myTable.col1) = 1 AND dbo.f2(myTable.col2) = 1.

---

Microsoft Marketing should care for customer feedback on their consumer products, e.g. Windows & Office.

Answer 7

I suggest you to try variation WITH CASE expression. It has more chances to be shortcutted when two conditions joined with AND.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

so, basically, it seems there is no general answer to that question. Am I right with this assumption?

Yes.   SQL might shortcut it or it might not.  It might do the test on f1 first and then f2 or do f2 first and then f1.  If you have calculations in the SELECT clause - for example Select (Col1 + 2 * Col2) / Col3 -and have a WHERE clause, SQL can do the WHERE first followed by the calculations or do the calculations first followed by the WHERE clause.

The only rule is SQL can do anything it chooses as long as it returns the correct result.  So SQL reorders the query to produce the most efficient query it can that produces the correct result.

Tom

Answer 9

Thanks, Naomi, I will give it a try.

Thanks to all of you for your valuable information!

---

Microsoft Marketing should care for customer feedback on their consumer products, e.g. Windows & Office.

Answer 10

This one of many reasons why we do not use UDFs. They screw up the optimizer. It might or might not short-circuit them, but that misses a fundamental principle. SQL is a declarative language based on sets and recursive function theory. Functional progamming is based on never having side effects.  Have you seen F#, Haskell, LISP, et al?

This progamming model is why we do not have BOOLEAN flags in SQL; we discover the state of the universe of discourse with predicates instead.

You are trying to write your old language in SQL. Stop it. If thart was an actual query, post it and we can try to fix it.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 11

This progamming model is why we do not have BOOLEAN flags in SQL; we discover the state of the universe of discourse with predicates instead.

There's a BIT type... is that not the same thing as a BOOL ?

---

Thanks! Josh Ash

Answer 12

No, BIT is a numeric data type in T-SQL. It has the values {0, 1, NULL} which is not what you assembly language guys mean by that data type. Since it is dialect, it should never be used. At one time it was {0,1} and non-RDBMS guys wrote code that depending on it behaving as if it were still in assembly language.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 13

Thanks, Celko, for your proposal, but no, unfortunately there is no room for optimization.

I have encapsulated the creation of a rowset into a function in order to re-use SELECT code and keep the creation of the result set maintainable at one single place.

The problem is that this function gets called for every row in the outer statements using it. So it will take time to run the inner SELECT statements over and over again.

I was hoping to shortcut some calls to the UDF by prepending some filtering predicates in the WHERE clause of the outer SELECT statement.

Actually, I would have expected that the optimizer would have always regarded UDF as "expensive" and, thus, would always have checked for "simple" WHERE predicates in the result set in advance (where applicable) before calling a UDF.

---

Microsoft Marketing should care for customer feedback on their consumer products, e.g. Windows & Office.

Answer 14

Well, I guess it's better to be an assembly programmer than a punchcard programmer..

I've seen BIT columns used as booleans in heaps of tables out in the field. It seems to work quite well. 

---

Thanks! Josh Ash