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

Introduction

Using IS NULL in filtered indexes can cause issues if the column is not part of the index key or explicitly included as a non-key column, leading to inefficient query execution.

Description

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:

-- Define a filtered index with a potential issue
CREATE 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.

How to fix

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:

-- Example 1: Inefficient index usage due to missing key or included column
CREATE INDEX IX_Example1 ON Example.Table1(column1) WHERE column2 IS NULL;
SELECT column1 FROM Example.Table1 WHERE column2 IS NULL;
SELECT column1, column2 FROM Example.Table1 WHERE column2 IS NULL;

-- Example 2: Corrected by adding column2 to the index key
CREATE INDEX IX_Example2 ON Example.Table2(column1, column2) WHERE column2 IS NULL;
SELECT column1 FROM Example.Table2 WHERE column2 IS NULL;

-- Example 3: Corrected by including column2 in the index
CREATE INDEX IX_Example3 ON Example.Table3(column1) INCLUDE(column2) WHERE column2 IS NOT NULL;
SELECT 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.

Additional Information

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)

Example Test SQL

CREATE NONCLUSTERED INDEX New_i_action_filt_action_date_type
 ON dbo.filter_test (action_type) WHERE action_date IS NULL

CREATE NONCLUSTERED INDEX New_i_action_filt_action_date_type
 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

Analysis Rules