A Filtered Index, which is an
optimized non-clustered index, allows us to define a filter predicate, a WHERE
clause, while creating the index. The B-Tree containing rows from the filtered
index will contain only those rows which satisfy the filter criteria used while
creating the index. This optimized index offers several benefits over a full
table non-clustered index as follows:
- As discussed above, the filtered index contains only those rows which satisfy the defined filter criteria. As a result it reduces the storage space requirement for the index. In the example below I will explain it more.
- The filtered statistics or statistics for a filtered index are more compact and accurate, because they consider only the rows in the filtered index and the smaller size of the filtered index reduces the cost/overhead of updating the statistics.
- The impact of data modification is less with a filtered index as it is updated only when the data of the index is impacted or a new record is inserted matching the filter criteria.
- Maintenance costs will be reduced as well since only a subset of rows will be in consideration while re-organizing or rebuilding the index.
- And most important, as it is an optimized non-clustered index, the query performance will improve if only a subset of data, which is covered by the filtered index criteria, is required.
