HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Why filtered index on IS NULL value is not used?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
whynullusedvalueindexnotfiltered

Problem

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

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 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.