patternsqlMajor
Why filtered index on IS NULL value is not used?
Viewed 0 times
whynullusedvalueindexnotfiltered
Problem
Assume we have a table definition like this:
And a filtered non-clustered index like this:
Why this index is not "covering" for this query:
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
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 GroupByColumnI'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
Solution
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/d365community/idea/ffbf409a-5125-ec11-b6e6-000d3a4f0da0
And as a workaround include the
No good reason. That is a covering index for that query.
Please vote for the feeback item here: https://feedback.azure.com/d365community/idea/ffbf409a-5125-ec11-b6e6-000d3a4f0da0
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)Code Snippets
CREATE NONCLUSTERED INDEX IX_MyTab_GroupByColumn
ON MyTab (GroupByColumn) include (WhereColumn)
WHERE (WhereColumn IS NULL)Context
StackExchange Database Administrators Q#217046, answer score: 29
Revisions (0)
No revisions yet.