Filtered index not used and key lookup with no output - by GilaMonster

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 454744 Comments
Status Closed Workarounds
Type Bug Repros 16
Opened 5/20/2009 9:45:37 AM
Access Restriction Public


A query with an IS NULL predicate that precisely matches a filtered index predicate where the index is covering gets a plan that uses a clustered index scan. If the index is forced via a hint then the expected nonclustered index scan is seen, but there's a key lookup present in the plan as well that's not looking anything up. It's output list is empty.

If the predicate of the query and the index is inverted (to IS NOT NULL) then the index is selected as expected and there is no key lookup present.
Sign in to post a comment.
Posted by xor88 on 1/9/2016 at 6:16 AM
This particular issue has hit us multiple times in the past. It reduces the usefulness of filtered indexes. It also furthers the impression that filtered indexes are a feature that is hard to control and get reliable.

Please consider addressing this.
Posted by Microsoft on 4/10/2010 at 11:08 AM
Dear Customer,

Thank you for pointing this out. After further investigation, we have discovered that this is actually not a bug but rather a known gap in functionality. This is now an active DCR for a future release of SQL Server.

Thank You,
Mohammed Ali
Posted by Microsoft on 6/3/2009 at 4:10 PM
Dear Customer,

Thanks for reporting this issue to us. It is valid, and we expect to release a fix in SQL Server Service Pack 2.

Senior Program Manager
SQL Engine