SA0270 : A filtered index created with the IS NULL predicate is not used in SQL Server

The topic describes the SA0270 analysis rule.

Message

A filtered index created with the IS NULL predicate is not used in SQL Server

Description

The rule checks T-SQL code for creation of filtered indexes using CREATE INDEX statements and reports when the IS NULL predicate is used with a column that is not included in the index structure.

SQL Server will not use the index when the IS NULL predicate filtering column is not a key column in the index, or included column in the filtered index definition, or is not used in the query results.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Performance Rules, Bugs

Additional Information
Example Test Script
SQL
1CREATE NONCLUSTERED INDEX New_i_action_filt_action_date_type
2 ON dbo.filter_test (action_type) WHERE action_date IS NULL
3
4CREATE NONCLUSTERED INDEX New_i_action_filt_action_date_type
5 ON dbo.filter_test (action_type, action_date) WHERE action_date IS NOT NULL

Analysis Results

  Message Line Column
1 SA0270 : The filtering column must be added as an included column for the index to be considered by SQL Server. 2 40
See Also

Other Resources