SA0270 : A filtered index created with the IS NULL predicate is not used in SQL Server
The topic describes the SA0270 analysis rule.
A filtered index created with the IS NULL predicate is not used in SQL Server
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.
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:
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
1CREATE INDEX IX_Example2 ON Example.Table2(column1, column2) WHERE column2 IS NULL 2SELECT column1 FROM Example.Table2 WHERE column2 IS NULL
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
The rule has a Batch scope and is applied only on the SQL script.
Rule has no parameters.
The rule does not need Analysis Context or SQL Connection.
Performance Rules, Bugs
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
|1||SA0270 : The filtering column must be added as an included column for the index to be considered by SQL Server.||2||40|