Microsoft SQL Server 2014 Unleashed

Filtered Indexes and Statistics

As discussed earlier in this chapter, a nonclustered index contains a row for every row in the table, even rows with a large number of duplicate key values where the nonclustered index will not be an effective method for finding those rows. For these situations, SQL Server 2014 provides filtered indexes. Filtered indexes are an optimized form of nonclustered indexes, created by specifying a search predicate when defining the index. This search predicate acts as a filter to create the index on only the data rows that match the search predicate. This reduces the size of the index and essentially creates an index that covers your queries, which return only a small percentage of rows from a well-defined subset of ...