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