# Filtered Indexes: Selective Optimization > [!Note] > `**Filtered indexes** improve performance and reduce storage by indexing only a **subset of rows** defined by a **WHERE** predicate, yielding **smaller structures**, **faster maintenance**, and **more accurate statistics** for targeted queries.` A filtered index is a **nonclustered index** that applies a **filter predicate** to include only rows meeting a specific condition. By narrowing the indexed set, SQL Server maintains a lighter B-Tree and generates statistics that accurately reflect the filtered data distribution. This selective approach minimizes I/O for pertinent queries, accelerates execution, and lowers the overhead of index maintenance. Filtered indexes are particularly effective when a column contains many **nulls** or **repeated values**, but only one or a few values are queried frequently. In such scenarios, a conventional index must cover the entire table, whereas a filtered index remains compact and highly selective. --- Below is a concise tabular summary of filtered index characteristics: |**Aspect**|**Details**| |---|---| |**Definition**|A **nonclustered index** that only stores rows satisfying a specified **WHERE** condition.| |**Advantages**|- **Smaller index size** reduces I/O and storage.- **Faster maintenance** (rebuilds and updates).- **More precise statistics** for optimizer.| |**Use Cases**|- Columns with many **NULL** values.- Columns with frequent queries on a **single or few values**.- Scenarios requiring highly **selective** access.| |**Considerations**|- The filter predicate must match query patterns exactly.- Over-filtering may lead optimizer to ignore the index.- Monitor usage to ensure benefit.| --- ## ## References - Korotkevitch, D. (2022). _SQL Server advanced troubleshooting and performance tuning: Best practices and techniques_. O’Reilly Media. - Nevarez, B. (2022). _SQL Server query tuning and optimization: Optimize Microsoft SQL Server 2022 queries and applications_. Packt Publishing.