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.
Best Answer
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.