Filtered Index in SQL Server

What is a Filtered Index?

A filtered index is a nonclustered index that includes a WHERE clause to index a subset of rows in a table.

Unlike a full index that covers all rows, a filtered index targets specific rows, reducing index size and improving query performance for those rows.

• Indexes only rows that satisfy the filter predicate.
• Useful for columns with many NULLs or low selectivity.
• Can improve performance and reduce storage costs.

Creating a Filtered Index

To create a filtered index, use the CREATE INDEX statement with a WHERE clause specifying the filter condition.

The filter condition must be a deterministic Boolean expression.

• Filtered indexes are nonclustered indexes.
• The WHERE clause defines which rows are included.
• Filter predicates can use comparisons, IS NULL, or other conditions.

Using Filtered Indexes Effectively

Filtered indexes are most effective when queries filter on the same condition as the index's filter predicate.

They reduce index maintenance overhead and storage compared to full indexes.

However, they require careful design to ensure the filter matches query predicates.

• Use filtered indexes for highly selective subsets of data.
• Avoid overly broad filters that reduce benefits.
• Keep statistics updated for accurate query plans.

Limitations and Considerations

Filtered indexes have some limitations to consider before use.

They cannot be used as clustered indexes.

Queries must include the filter predicate to benefit from the index.

• Filtered indexes do not support all index features like included columns with certain data types.
• Maintenance operations like rebuilds require attention to filtered indexes.
• Filtered indexes may not be used if the query optimizer cannot match the filter predicate.