Why filtered index on IS NULL value is not used?
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)
I had the same issue I think when doing some testing weeks ago. I have a query with a primary predicate that requires that results returned have a NULL closedatetime and I thought about using a filtered index as 25K of 2M+ records are NULL and this figure will decrease very soon.
The filtered index didn't get used - I assumed due to 'non-uniqueness' or commonality - until I found a Microsoft support article that says:
To resolve this issue, include the column that is tested as NULL in the returned columns. Or, add this column as include columns in the index.
So adding the column to the Index (or Include) seems to be the official MS response.