Select "where clause" evaluation order

Question

In Sql Server 2005 when I have multiple parameters do I have the guarantee that the evaluation order will always be from left to right?

Using an example:

select a from table where c=1 and d=2

In this query if the "c=1" condition fails the "d=2" condition will never be evaluated?

PS- "c" is an integer indexed column, d is a large varchar and non indexable column that requires a full table scan

update I was trying to avoid performing two queries or conditional statements, I just need something like: if "c condition" fails there's a way to avoid performing the heavy "d condition", since it's not needed in my case.

Answer 1

There are no guarantees for evaluation order. The optimizer will try to find the most efficient way to execute the query, using available information.

In your case, since c is indexed and d isn't, the optimizer should look into the index to find all rows that match the predicate on c, then retrieve those rows from the table data to evaluate the predicate on d.

However, if it determines that the index on c isn't very selective (although not in your example, a gender column is rarely usefully indexed), it may decide to do the table scan anyway.

To determine execution order, you should get an explain plan for your query. However, realize that that plan may change depending on what the optimizer thinks is the best query right now.

Answer 2

SQL Server will generate an optimized plan for each statement it executes. You don't have to order your where clause to get that benefit. The only garuntee you have is that it will run statements in order so:

SELECT A FROM B WHERE C
SELECT D FROM E WHERE F

will run the first line before the second.

Answer 3

One way to control the evaluation order is with the CASE expression.

[Edit]

The popular opinion I was trying to express was:

You cannot depend on expression evaluation order for things like “WHERE OR “, since the optimizer might choose a plan that evaluates the second predicate before the first one. But order of evaluation of the expressions in a CASE statement is fixed, so you can depend on deterministic short circuit evaluation of a CASE statement.

It does get a bit more complicated than that as explained in the site below:

Link

Answer 4

You can look at the execution plan of the query and determine what it's actually trying to do. I think the query engine of SQL Server is supposed to be doing this type of scanning and will intelligently translate it into operations. Like, if you do "expensive-op AND false", it will quickly evaluate to false.

From what I've learned, what you type is (and can be) different from what's actually executed. You're merely telling the server what type of results you expect. How it gets the answer does not correlate left-to-right of the code you provide.

Answer 5

If you want to be sure you can check the Query Execution Plan. The Execution Plan that MSSQL builds/optimizes is smart enough to check the indexed column before a varchar column.

Answer 6

Short-Circuit is done when the condition we are referencing only includes literals or constants. So for example lets say we have a table TableA which has column num with all positive numbers from 1 to 10 and then if i write this query.

Select num from TableA WHERE TableA.num < 0 AND 1/0 = 10.

It will result in error.

Is compiler smart enough to determine that my second clause is consists of constants so it should evaluate that before evaluating clause which requires any scan from table or index?

Answer 7

The MS SQL Server query optimizer does short circuit, yes. Guaranteed.

Run this:

select 1 where 1 = 0 and 1 / 0 = 10

It will run just fine and not error even though you're dividing by zero because the query optimizer will short-circuit evaluate the where clause. This has implications for any where clause where you're "and"-ing and one of the and parts is a constant.