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 location is null. Records that pass the lookup are selected.

Why on Earth? location is 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?

  • It looks to me that lookup is performed because you have rank column in your query which is not part of the index
    – a1ex07
    Feb 15, 2012 at 16:50
  • I am having such issues from time to time as well, so I am not using this filtered index thing as much as I would like. Instead, I use indexed views or normal indexes.
    – A-K
    Feb 15, 2012 at 17:03
  • 1
    @a1ex07 How is rank not part of the index?
    – GSerg
    Feb 15, 2012 at 17:09
  • @AlexKuznetsov Me too, I use index hints for that normally now, since the filtered indexes are usually tuned for a particular query
    – JNK
    Feb 15, 2012 at 17:56
  • @GSerg: nm, sorry, I missed it
    – a1ex07
    Feb 15, 2012 at 18:03

1 Answer 1


This has been previously raised on Connect and Closed as "Won't Fix".

We'll close this because it seems a narrow scenario and we don't see doing anything about it in the forseeable future.

That's quite disappointing IMO. Perhaps upvote and comment that item.

Edit: BOL has an example of using WHERE EndDate IS NOT NULL in a filtered index and that does not do a key lookup but replacing IS NOT NULL with IS NULL does lead to a lookup so it appears as though sometimes it is avoided...

  • Yes, it sometimes does the right thing. For instance, it does the right thing in regard of dismantled in my example, I don't have to include it in the index.
    – GSerg
    Feb 15, 2012 at 20:20
  • I mostly use filtered indexes to enforce conditional uniqueness. Other than that, I was encountering issues and gave up on it for now. How do you use them?
    – A-K
    Feb 16, 2012 at 2:32
  • @AlexKuznetsov To enforce uniqueness ignoring null fields, yes. Sometimes for actual queries though, at which point I have to test to see what filtered columns I will need to include.
    – GSerg
    Feb 16, 2012 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.