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.

How to fix

Add the column to the index definition as a key column or as an included column.

For example IX_Example1 is not used, while IX_Example2 and IX_Example3 are used:

SQL
1CREATE INDEX IX_Example1 ON Example.Table1(column1) WHERE column2 IS NULL
2SELECT column1 FROM Example.Table1 WHERE column2 IS NULL
3SELECT column1, column2 FROM Example.Table1 WHERE column2 IS NULL

SQL
1CREATE INDEX IX_Example2 ON Example.Table2(column1, column2) WHERE column2 IS NULL
2SELECT column1 FROM Example.Table2 WHERE column2 IS NULL

SQL
1CREATE INDEX IX_Example3 ON Example.Table3(column1) INCLUDE(column2) WHERE column2 IS NOT NULL
2SELECT column1 FROM Example.Table3 WHERE column2 IS NOT NULL

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.

Effort To Fix
2 minutes per issue.
Categories

Performance Rules, Bugs

Additional Information
Example Test SQL
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