SQL Server 2008 - Filtered Index

If you see the Create Index Syntax in SQL Server 2008 books online, you can see a new option called “ [ WHERE ]” which stands for Filtered Index feature.

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.

Suppose you have a table which stored the history (not so relevant to OLTP application ) data also. The query on that table is mostly on the current data and you want to index only on current data for eg. DateofPurchace > ‘1-1-2007’ kind. Another example would be , you have a product catalog and you want to show only the active products to the customer.

Eg.
CREATE NONCLUSTERED INDEX NCI_OrderDetailsFilteredIndex
ON OrderDetail (OrderDetailID, DateofPurchase)
WHERE DateofPurchase >’1-1-2007’

Filtered Index Design Guidelines

Filtered Index Feature Support
In general, the Database Engine and tools provide the same support for filtered indexes that they provide for nonclustered full-table indexes, considering filtered indexes as a special type of nonclustered indexes. The following list provides notes about tools and features that fully support, do not support, or have restricted support for filtered indexes.
• ALTER INDEX supports filtered indexes. To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.
• The missing indexes feature does not suggest filtered indexes.
• The Database Engine Tuning Advisor considers filtered indexes when recommending index tuning advice.
• Online index operations support filtered indexes.
• Table hints support filtered indexes, but have some restrictions that do not apply to non-filtered indexes. These are explained in the following section.