This just drives me mad.
Consider a simple table with irrelevant columns removed:
create table boxes ( row_id int not null identity(1,1) primary key, location varchar(15) null, dismantled bit not null default 0, rank int not null default 0 /* irrelevant columns, text and numbers */ )
For a certain important query, I want a filtered index:
create nonclustered index anindex ON boxes (rank) where (dismantled=0 and location is null)
This creates an index that contains about 150 records out of about 150k. Which is fine.
Now we query the table:
select top (1) row_id from boxes where dismantled = 0 and location is null order by rank;
The execution plan is weird. Index scan on
anindex accompanied by a bookmark lookup using the seeked
row_id to confirm that
null. Records that pass the lookup are selected.
Why on Earth?
null by the very definition of the used index, is it not?
Now, if I do a stupid thing and include
location in the filtered index...
create nonclustered index anindex ON boxes (location, rank) where (dismantled=0 and location is null)
Then all of a sudden the execution plan is great, index seek on
anindex with no lookups.
This is just one example, I stumble upon this issue every time I want to use a filtered index. Every time I end up forced to include the useless fields, upon which the index is filtered, to the list of indexed fields, and only then the server uses the index properly; otherwise, it either scans it or ignores it completely.
What gives? Is it a recommended practice to include the useless fields?