Peculiar case with SQL Server, indices and parameters
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开发者_StackOverflow社区 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?
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.
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.
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.
精彩评论