sql server 2008 - Filtered index condition is ignored by optimizer

Question

Assume I'm running a website that shows funny cat pictures. I have a table called CatPictures with the columns Filename, Awesomeness, and DeletionDate, and the following index:

create nonclustered index CatsByAwesomeness
on CatPictures (Awesomeness) 
include (Filename)
where DeletionDate is null

My main query is this:

select Filename from CatPictures where DeletionDate is null and Awesomeness > 10

I, as a human being, know that the above index is all that SQL Server needs, because the index filter condition already ensures the DeletionDate is null part.

SQL Server however doesn't know this; the execution plan for my query will not use my index:

Even if adding an index hint, it will still explicitly check DeletionDate by looking at the actual table data:

(and in addition complain about a missing index that would include DeletionDate).

Of course I could

include (Filename, DeletionDate)

instead, and it will work:

But it seems a waste to include that column, since this just uses up space without adding any new information.

Is there a way to make SQL Server aware that the filter condition is already doing the job of checking DeletionDate?

Answer 1

No, not currently.

See this connect item. It is Closed as Won't Fix. (Or this one for the IS NULL case specifically)

The connect item does provide a workaround shown below.

Posted by RichardB CFCU on 29/09/2011 at 9:15 AM

A workaround is to INCLUDE the column that is being filtered on.

Example:

CREATE NONCLUSTERED INDEX [idx_FilteredKey1] ON [dbo].[TABLE] 
(
    [TABLE_ID] ASC,
    [TABLE_ID2] ASC
)
INCLUDE ( [REMOVAL_TIMESTAMP]) --explicitly include the column here
WHERE ([REMOVAL_TIMESTAMP] IS NULL)