Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 '12 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. –  AlexKuznetsov Feb 15 '12 at 17:03
1  
@a1ex07 How is rank not part of the index? –  GSerg Feb 15 '12 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 '12 at 17:56
    
@GSerg: nm, sorry, I missed it –  a1ex07 Feb 15 '12 at 18:03
add comment

1 Answer

up vote 3 down vote accepted

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...

share|improve this answer
    
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 '12 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? –  AlexKuznetsov Feb 16 '12 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 '12 at 9:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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