Gianluca Sartori (12/30/2010)
Daniel Ruehle (12/30/2010)
One easy way to make sure short circuiting works the way you want it is using case statements:
select*
from
Person
where
1 = 1
and CreateDateTime > getdate() - 30
and case
when Age > 90 then 1
when Age < 5 then 0
when Gender = 'Male' then 1
when LastName like 'SAM%' then 1
else 0
end = 1
This gets records for all people over the age of 90, males of age 5 or more and anyone with a last name that starts with the letters SAM. Notice that the integer checks are done first as they are the easiest to evaluate and the expensive like expression is last. The documentation for the case statement explicity says:
Evaluates, in the order specified, Boolean_expression for each WHEN clause.
so this is like an explicit short circuit if you would like.
You're right, Daniel. CASE is guranteed to evaluate expressions in the exact order they appear.
What is questionable is the time you save by pushing "expensive tests" down. Unless you're working with billion row tables, you wouldn't even notice the difference. It's the query plan that decides how fast the query will run, not the number of expressions to evaluate.
Agree, but if the query plan says its going to scan the table, then it does come down to how long does it take to process each row. Since you aren't guaranteed the order that SQL Server will evaluate the conditions when just using boolean logic, it can choose to do then in an inefficient manner, which I believe was the jist of the article. In scenarios where it might matter, this gives you absolute control the order.