Microsoft® SQL Server 2008 R2 Unleashed

Creating and Using Filtered Indexes

To define filtered indexes, you use the normal CREATE INDEX command but include a WHERE condition as a search predicate to specify which data rows the filtered index should include. In the current implementation, you can specify only simple search predicates such as IN; the comparison operators IS NULL, IS NOT NULL, =, <>, !=, >, >=, !>, <, <=, !<; and the logical operator AND. In addition, filtered indexes cannot be created on computed columns, user-defined data types, Hierarchyid, or spatial types.

For example, assume you need to search only the sales table in the bigpubs2008 database for sales since 9/1/2008. The majority of the rows in the sales table have order dates prior to 9/1/2008. To create a filtered ...