Peculiar case with SQL Server, indices and parameters

Question

I have a table, let's call it History. The primary key (aka Clustered Index) is called HIST_ID. The table has some 2300 rows in the development DB. Now consider the following two queries:

Query 1:

declare @x int
set @x = 14289

select * from History where hist_id=@x

Query 2:

declare @x int
set @x = 14289

select * from History where hist_id=@x or @x is null

The only difference is the or @x is null at the end. However the first query does an index seek, the second - index scan. What gives?

Pre-emptive reply - no, option(recompile) doesn't help.

Added: I'd like some solid argumented facts, not guesses. I can guess a dozen possible reasons for this myself. But what is the real problem here?

Answer 1

I guess the optimizer determines it is beneficial. The alternative would be to use the same plan as if you had written

select * from History where hist_id=@x
union all
select * from History where @x is null

You can rewrite the query in this way, but I'm pretty sure the optimizer is capable of doing this by itself. How many null values do you have?

Edit: Turns out I misread the question and thought you wanted WHERE (@x = hist_id OR hist_id is null). In fact you want a dynamic criterion. Check out this article. Note that this your kind of query was supposed to work in SQL2k8 if you specify WITH(RECOMPILE), but due to a nasty bug this support was removed.

Answer 2

I would suggest that the plan is being produced separate to the parameter being passed in / used, so in essence there is a requirement (depending on the value of @x) to return every row. As such the query plan is dealing with it's worst case scenario of the parameters it can receive.

e.g. If the input for @x was null, then the query would be forced to return every row since every row would satisfy a literal equation / predicate that always returned true. For the query plan to cover every value of @x, it must generate a plan that performs a scan.

Answer 3

Of course it's an index scan.

A clustered index scan = table scan because you have no sensible predicate for "@x IS NULL".

The parameterised, cached plan does is general and will work for @x = NULL or @x = value. If you don't define @x, you should get the same plan.

If you coded "12345 IS NULL" then this is detected and ignored.

I can't find an blog article on how constants are treated in query plans. The gist is that they are generalised and short circuits don't happen to allow plan reuse.