- 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.)
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.
CREATE NONCLUSTERED INDEX New_i_action_filt_action_date_type ON dbo.filter_test (action_type) include (action_date) WHERE action_date IS NULL
Article ID: 3051225 - Last Review: 28 Apr 2015 - Revision: 1