Filtered Indexes (Database Engine)
A filtered index is a non‑clustered index that includes a WHERE clause to index a subset of rows in a table. Filtering can reduce index size and improve query performance, especially for columns with a high proportion of NULL or default values.
Benefits
- Smaller index size leading to faster scans and less storage.
- Improved maintenance: less data to rebuild or reorganize.
- Better query performance for selective workloads.
- Potentially lower I/O and memory usage.
Syntax
CREATE NONCLUSTERED INDEX index_name
ON table_name (column1 [ASC|DESC] [, column2 ...])
WHERE filter_predicate;
Examples
Example 1 – Indexing non‑NULL values
CREATE NONCLUSTERED INDEX IX_Order_CustomerId
ON dbo.[Order] (CustomerId)
WHERE CustomerId IS NOT NULL;
Example 2 – Indexing a specific status
CREATE NONCLUSTERED INDEX IX_Employee_Active
ON dbo.Employee (HireDate, DepartmentId)
WHERE IsActive = 1;
Performance considerations
When a query can use the filtered index, the optimizer evaluates the index’s predicate to determine if the rows qualify. Use the SET STATISTICS IO ON and SET STATISTICS TIME ON commands to compare plans before and after adding a filtered index.
| Scenario | Impact |
|---|---|
| Highly selective predicate | Significant improvement |
| Low selectivity | Minimal gain; consider full index |
Limitations
- Filtered indexes cannot be used on
XML,FILESTREAM, orSPARSEcolumns. - Cannot be created on computed columns that are not persisted.
- Inclusion of
INCLUDEcolumns must be compatible with the filter predicate. - Statistical information is maintained separately for the filtered subset.