Filtered Indexes?

Filtered index:

Filtered indexes for SQL Server were introduced in SQL Server 2008. Put simply, filtered indexes are nonclustered indexes that have the addition of a WHERE clause. Although the WHERE clause in a filtered index allows only simple predicates, it provides notable improvements over a traditional nonclustered index. This allows the index to target specific data values – only records with certain values will be added to the index – resulting in a much smaller, more accurate, and more efficient index.
The basic syntax for creating a filtered index is:

CREATE NONCLUSTERED INDEX <index name> ON <table> (<columns>) WHERE <criteria>; GO

Advantages of using filtered indexes

Reduced index maintenance costs. Insert, update, delete, and merge operations are not as expensive, since a filtered index is smaller and does not require as much time for reorganization or rebuilding.
Reduced storage cost. The smaller size of a filtered index results in a lower overall index storage requirement.
More accurate statistics. Filtered index statistics cover only the rows the meet the WHERE criteria, so in general, they are more accurate than full-table statistics.
Optimized query performance. Because filtered indexes are smaller and have more accurate statistics, queries and execution plans are more efficient.

Limitations

• Statistics may not get updated often enough, depending on how often filtered column data is changed. Because of how SQL Server decides when to update statistics (when about 20% of a column’s data has been modified), statistics could become quite out of date. The solution to this issue is to set up a job to run UPDATE STATISTICS more frequently.
• Filtered indexes cannot be created on views. However, a filtered index on the base table of a view will still optimize a view’s query.
• XML indexes and full-text indexes cannot be filtered. Only nonclustered indexes are able to take advantage of the WHERE clause.
• The WHERE clause of a filtered index will accept simple predicates only.