A filtered index that you create together with the IS NULL predicate is not used in SQL Server

This article helps you resolve the problem that occurs when you create the index together with the Column IS NULL predicate expression in SQL Server.

Original product version:   SQL Server
Original KB number:   3051225

Symptoms

Consider the following scenario:

  • You create a filtered index together with the Column IS NULL predicate expression in SQL Server.
  • The Column field is not included in the index structure. (That is, the Column field is not a key or included column in the filtered index definition.)

For example, you create the following query:

CREATE UNIQUE CLUSTERED INDEX i_action_rn on dbo.filter_test (rn)
CREATE NONCLUSTERED INDEX i_action_filt_action_date_type ON dbo.filter_test (action_type)
WHERE action_date IS NULL

Note

This query does not use the following filtered index:

select count(*) from dbo.filter_test where action_date is null and action_type=1

In this scenario, the filtered index is not used. Instead, the clustered index is used.

Cluster index scan

Resolution

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.

CREATE NONCLUSTERED INDEX New_i_action_filt_action_date_type ON dbo.filter_test (action_type) include (action_date) WHERE action_date IS NULL

Index Seek

More information