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

In SQL Server, filtered indexes can significantly enhance query performance by indexing only specific subsets of data. However, a common problem arises when using the IS NULL condition in a filtered index. The column specified in the IS NULL predicate must be part of the index key or explicitly included as a non-key column. Failing to do so may prevent SQL Server from utilizing the index, leading to inefficient query execution.

For example:

1-- Define a filtered index with a potential issue
2CREATE INDEX idx_Example ON MyTable(Col1) WHERE Col2 IS NULL;

In this example, if Col2 is not part of the index key or included in the index, SQL Server won’t leverage idx_Example effectively. This oversight can result in full table scans instead of efficient index seeks.

• Queries using IS NULL on Col2 could run slower due to missing index utilization.

• Potential increase in query execution time and resource usage.

Ensure the column in the IS NULL predicate is part of the index key or included to enhance index utilization.

Follow these steps to address the issue:

1. Add the column to the index key: If the column causing the problem is critical for query performance, include it as part of the index key. This ensures SQL Server will use the index when filtering by this column.

2. Include the column in the index as an included column: If adding the column as a key column is not appropriate, include it in the index as an included column. This allows SQL Server to efficiently use the index even though the column is not part of the index key.

For example:

 1-- Example 1: Inefficient index usage due to missing key or included column
 2CREATE INDEX IX_Example1 ON Example.Table1(column1) WHERE column2 IS NULL;
 3SELECT column1 FROM Example.Table1 WHERE column2 IS NULL;
 4SELECT column1, column2 FROM Example.Table1 WHERE column2 IS NULL;
 5
 6-- Example 2: Corrected by adding column2 to the index key
 7CREATE INDEX IX_Example2 ON Example.Table2(column1, column2) WHERE column2 IS NULL;
 8SELECT column1 FROM Example.Table2 WHERE column2 IS NULL;
 9
10-- Example 3: Corrected by including column2 in the index
11CREATE INDEX IX_Example3 ON Example.Table3(column1) INCLUDE(column2) WHERE column2 IS NOT NULL;
12SELECT 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

A filtered index that you create together with the IS NULL predicate is not used in SQL Server

Why filtered index on IS NULL value is not used? (Stack Exchange)

Other Resources