Microsoft SQL Server 2014 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 predicate 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 the SalesOrderHeader table in the AdventureWorks2012 database for sales since 6/1/2008. The majority of the rows in the SalesOrderHeader table have order dates prior to 6/1/2008. ...