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 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. ...