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. ...
Get Microsoft SQL Server 2014 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.