SQL Server Interview Question #130

What is a filtered index?

Indexes & Query Performance Senior Advanced

Detailed Explanation

A filtered index is a nonclustered index built only on rows satisfying a WHERE predicate. It can be smaller and cheaper to maintain than indexing the entire table when queries repeatedly target a well-defined subset.

Common examples include active rows, non-NULL values, unprocessed queue items, or a specific status. The query predicate must be compatible with the filter for SQL Server to use the index effectively.

Code Example

CREATE INDEX IX_Products_Active_Category
ON dbo.Products(CategoryId)
INCLUDE (Name, Price)
WHERE IsActive = 1;