Why filtered index on IS NULL value is not used? - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Why filtered index on IS NULL value is not used?

sql-server add tag

jerik1 (imported from SE)

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:

[![enter image description here][1]][1]


The KeyLookup is for the WhereColumn IS NULL predicate.

Here is the plan: [https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7][2]


  [1]: https://i.stack.imgur.com/JTu56.gif
  [2]: https://www.brentozar.com/pastetheplan/?id=SJcbLHxO7

Top Answer

david browne microsoft (imported from SE)

>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][1] include the `WhereColumn` in the filtered index:

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


  [1]: https://support.microsoft.com/en-gb/help/3051225/a-filtered-index-that-you-create-together-with-the-is-null-predicate-i

Answer #2

SteveO (imported from SE)

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][1] 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.


  [1]: https://support.microsoft.com/en-gb/help/3051225/a-filtered-index-that-you-create-together-with-the-is-null-predicate-i

2 Answers