A new feature in SQL Server 2008 (which has not been well publicized) is filtered indexes. SQL Server Books Online (BOL) describes it this way:
A filtered index is an optimized nonclustered index, suited for queries that select a small percentage of rows from a table. It uses a filter predicate to index a portion of the data in the table. A well-designed filtered index can improve query performance, reduce storage costs, and reduce maintenance costs.
-- Sample Filtered Index for AdventureWorks CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate" ON Production.BillOfMaterials (ComponentID, StartDate) WHERE EndDate IS NOT NULL;
SQL Server BOL has this to to say about filtered indexes:
When to Use Filtered Indexes
Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Examples are:
- Sparse columns that contain only a few non-NULL values.
- Heterogeneous columns that contain categories of data.
- Columns that contain ranges of values such as dollar amounts, time, and dates.
- Table partitions that are defined by simple comparison logic for column values.