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

Applies to: SQL Server 2014 EnterpriseSQL Server 2014 EnterpriseSQL Server 2012 Enterprise

Symptoms


Consider the following scenario:
  • You create a filtered index together with the Column IS NULL predicate expression in Microsoft 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 

Notice that 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


See the following Microsoft Developer Network (MSDN) websites: