How does the T-SQL optimizer handle “and false”

Question

In SQL SERVER 2008 I'm thinking about a query such as:

SELECT ...
FROM ...
WHERE ...
  AND @a=1
UNION
SELECT ...
FROM ...
WHERE ...
  AND @a=2

Given that @a is clearly the same value, and therefore either the first SELECT returns results or the second SELECT does, will the query optimizer identify one of the queries as false and not run it? Would it be better for me to put them in an IF statement? The actual query is a bit more complicated than written of course, so the if would make it like 4 different queries, which would be a pain to maintain.

I'm hoping the optimizer is smart enough to see the 'and false' and not waste time with it.

Answer 1

With respect to your question

will the query optimizer identify one of the queries as false and not run it?

You'd need to check the execution plans. An example below where it doesn't.

DECLARE @P INT =1

SET STATISTICS IO ON;

SELECT name
FROM sys.objects
WHERE type='P' AND @P=1
UNION 
SELECT name
FROM master..spt_values
WHERE number >0 AND @P=0

Output

Table 'Worktable'. Scan count 0, logical reads 0
Table 'sysschobjs'. Scan count 1, logical reads 5

Notice that master..spt_values was not touched in the query execution. The reason for this is that the seek on this table is under a filter with a start up expression predicate of [@P]=(0). As this condition is not met the seek is never actually executed.

Both the Startup Expression and number of executions are shown in the operator tool tips as below.

With regard to the other part of your question

Would it be better for me to put them in an IF statement?

Yes probably despite that. You're then guaranteed that the behaviour that you get is that which you want. Also even though master..spt_values doesn't have any pages read above it does still have an IS lock taken out against it (which doesn't happen in the IF ... ELSE version)

Better still might be to use an If that calls separate stored procedures for each branch. You haven't shown the rest of your code but one possible issue with these mutually exclusive branches might be parameter sniffing. The batch will be compiled as per the first set of parameters seen. These might be wholly unsuitable for the other branch(es). Splitting into child batches allows each branch to be compiled separately taking into account the parameter values that actually occurred when that branch was executed.

Answer 2

SQL Server optimizer is closed source, so we cannot see what exactly is going on under the hood. Of course, there are tons of documentation, and just as surely there are lots of cases when this thing does not behave exactly as documented.

In my experience, all documentation from all vendors tends to get out of sync quite soon, sometimes before the first release ;). The only 100% accurate source of information is source code, pun intended.

So, even if you observe some behavior right now, there is no guarantee it cannot change later on. The reason: we have no idea what is involved, how the optimizer is making its decisions. So, we do not know if we have tested all the situations.

As such, I would keep it simple:

IF @a=1 BEGIN ;
  SELECT ... FROM ... ;
END ELSE BEGIN ;
  SELECT ... FROM ... ;
END ;

To reuse code and reduce maintenance costs, I would have both selects utilize an inline UDF to keep common functionality in one place.

Besides, as demonstrated in Martin Smith's excellent answer, UNION leads to a possibly unnecessary sort, which may be very expensive.

In general, it pays to write shorter, more specific queries - you have a better chance to get a good execution plan.

Answer 3

The query optimizer must come up with an execution plan that works for any value of @a. This plan will be executed when @a=1 and the very same execution plan will be executed when @a=2. Therefore by definition the answer to your question is NO: all queries in the UNION will be executed. There is simply no other way.