|Programming in MS SQL Server 2012|
A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
When to Use Filtered Index
Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Examples are:
01. Sparse columns that contain only a few non-NULL values.
02. Heterogeneous columns that contain categories of data.
03. Columns that contain ranges of values such as dollar amounts, time, and dates.
04. Table partitions that are defined by simple comparison logic for column values.
The following example describes to creates a Filter Index with sample SELECT query.
Query Output Screen
Limitation of Filtered Index
Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. In this case, you should use a full-table index instead of a filtered index.
Filtered indexes are defined on one table and only support simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view.
Views vs. Filtered Indexes
A view is a virtual table that stores the definition of a query; it has a broader purpose and functionality than a filtered index. The following table compares some of the functionality allowed in views with that of filtered indexes.
* * * * *