Why filtered index on IS NULL value is not used? Assume we have a table definition like this:

CREATE TABLE MyTab (
    ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
    ,GroupByColumn NVARCHAR(10) NOT NULL
    ,WhereColumn DATETIME NULL
    )


And a filtered non-clustered index like this:

CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn ON MyTab 
    (GroupByColumn)
WHERE (WhereColumn IS NULL) 


Why this index is not "covering" for this query:

SELECT 
    GroupByColumn
    ,COUNT(*)
FROM MyTab
WHERE WhereColumn IS NULL
GROUP BY GroupByColumn


I'm getting this execution plan:



The KeyLookup is for the WhereColumn IS NULL predicate.

Here is the plan: https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7
Why this index is not "covering" for this query:


No good reason.  That is a covering index for that query.

Please vote for the feeback item here: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896348-filtered-index-not-used-when-is-null-and-key-looku

And as a workaround include the WhereColumn in the filtered index:

CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn 
ON MyTab (GroupByColumn) include (WhereColumn)
WHERE (WhereColumn IS NULL)