Is the SQL WHERE clause short-circuit evaluated?

Question

ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf

6.3.3.3 Rule evaluation order

[...]

Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.

From the above, short circuiting is not really available.

If you need it, I suggest a Case statement:

Where Case when Expr1 then Expr2 else Expr3 end = desiredResult

Expr1is always evaluated, but only one of Expr2 and Expr3 will be evaluated per row.

I think this is one of the cases where I'd write it as if it didn't short-circuit, for three reasons.

1. Because for MSSQL, it's not resolved by looking at BOL in the obvious place, so for me, that makes it canonically ambiguous.

2. because at least then I know my code will work. And more importantly, so will those who come after me, so I'm not setting them up to worry through the same question over and over again.

3. I write often enough for several DBMS products, and I don't want to have to remember the differences if I can work around them easily.

I don't believe that short circuiting in SQL Server (2005) is guaranteed. SQL Server runs your query through its optimization algorithm that takes into account a lot of things (indexes, statistics, table size, resources, etc) to come up with an effective execution plan. After this evaluation, you can't say for sure that your short circuit logic is guaranteed.

I ran into the same question myself sometime ago and my research really did not give me a definitive answer. You may write a small query to give you a sense of proof that it works but can you be sure that as the load on your database increases, the tables grow to be bigger, and things get optimized and changed in the database, that conclusion will hold. I could not and therefore erred on the side of caution and used CASE in WHERE clause to ensure short circuit.

You have to keep in mind how databases work. Given a parameterized query the db builds an execution plan based on that query without the values for the parameters. This query is used every time the query is run regardless of what the actual supplied values are. Whether the query short-circuits with certain values will not matter to the execution plan.

I typically use this for optional parameters. Is this the same as short circuiting?

SELECT  [blah]
FROM    Emp
WHERE  ((@EmpID = -1) OR (@EmpID = EmpID))

This gives me the option to pass in -1 or whatever to account for optional checking of an attribute. Sometimes this involves joining on multiple tables, or preferably a view.

Very handy, not entirely sure of the extra work that it gives to the db engine.