Filtered Index Design Guidelines

A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Filtered indexes can provide the following advantages over full-table indexes:

• Improved query performance and plan quality

  A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

• Reduced index maintenance costs

  An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.

• Reduced index storage costs

  Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

Design Considerations

In order to design effective filtered indexes, it is important to understand what queries your application uses and how they relate to subsets of your data. Some examples of data that have well-defined subsets are columns with mostly NULL values, columns with heterogeneous categories of values and columns with distinct ranges of values. The following design considerations give a variety of scenarios for when a filtered index can provide advantages over full-table indexes.

Filtered Indexes for Subsets of Data

When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

For example, the AdventureWorks2008R2 database has a Production.BillOfMaterials table with 2679 rows. The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain NULL. The following filtered index would cover queries that return the columns defined in the index and that select only rows with a non-NULL value for EndDate.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

The filtered index FIBillOfMaterialsWithEndDate is valid for the following query. You can display the query execution plan to determine if the query optimizer used the filtered index. For information about how to display the query execution plan, see Analyzing a Query.

SELECT ProductAssemblyID, ComponentID, StartDate 
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL 
    AND ComponentID = 5 
    AND StartDate > '01/01/2008' ;
GO

For more information about how to create filtered indexes and how to define the filtered index predicate expression, see CREATE INDEX (Transact-SQL).

Filtered Indexes for Heterogeneous Data

When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data.

For example, the products listed in the Production.Product table are each assigned to a ProductSubcategoryID, which are in turn associated with the product categories Bikes, Components, Clothing, or Accessories. These categories are heterogeneous because their column values in the Production.Product table are not closely correlated. For example, Color, ReorderPoint, ListPrice, Weight, Class, and Style have unique characteristics for each product category. Suppose that there are frequent queries for Accessories which have subcategories 27-36. You can improve the performance of queries for Accessories by creating a filtered index on the Accessories subcategories.

The following example creates a filtered index on all products in the Accessories subcategories in the Production.Product table.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

The filtered index FIProductAccessories covers the following query because the query

results are contained in the index and the query plan does not include a base table lookup. For example, the query predicate expression ProductSubcategoryID = 33 is a subset of the filtered index predicate ProductSubcategoryID >= 27 and ProductSubcategoryID <= 36, the ProductSubcategoryID and ListPrice columns in the query predicate are both key columns in the index, and name is stored in the leaf level of the index as an included column.

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

Views vs. Filtered Indexes

A view is a virtual table that stores the definition of a query; it has a broader purpose and functionality than a filtered index. For more information on views, see Understanding Views and Scenarios for Using Views. The following table compares some of the functionality allowed in views with that of filtered indexes.

Allowed in expressions	Views	Filtered indexes
Computed columns	Yes	No
Joins	Yes	No
Multiple tables	Yes	No
Simple comparison logic in a predicate*	Yes	Yes
Complex logic in a predicate**	Yes	No

*For simple comparison logic in a predicate, see WHERE clause syntax in CREATE INDEX.

**For complex comparison logic in a predicate, see WHERE clause syntax for SELECT.

You cannot create a filtered index on a view. However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. The query optimizer considers a filtered index for a query that selects from a view if the query results will be correct. The following example creates a view with start dates after April 1, 2000 and a filtered index with start dates after August 1, 2000.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS 
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate 
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO

In the following example, the query selects start dates greater than September 1, 2004, which are all contained in the filtered index and the filtered view. The query optimizer considers the filtered index FIBillOfMaterialsByStartDate because it contains the correct results for the query.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040901';
GO

In the next example, the query selects start dates greater than June 1, 2004 which are all contained in the view, but not in the filtered index. The query optimizer does not consider the filtered index FIBillOfMaterialsByStartDate because the query can return different results by using the filtered index compared with the correct results when the query selects from the view.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040601';
GO

Indexed Views vs. Filtered Indexes

Filtered indexes have the following advantages over indexed views:

• Reduced index maintenance costs. For example, the query processor uses fewer CPU resources to update a filtered index than an indexed view.

• Improved plan quality. For example, during query compilation, the query optimizer considers using a filtered index in more situations than the equivalent indexed view.

• Online index rebuilds. You can rebuild filtered indexes while they are available for queries. Online index rebuilds are not supported for indexed views. For more information, see the REBUILD option for ALTER INDEX (Transact-SQL).

• Non-unique indexes. Filtered indexes can be non-unique, whereas indexed views must be unique.

For the above reasons, we recommend using a filtered index instead of an indexed view when possible. It is possible to use a filtered index instead of an indexed view when the following conditions are met: the view references only one table, queries do not return computed columns, and the view predicate uses simple comparison logic. For example, the following predicate expression is allowed in a view definition but not in filtered indexes, because it contains the LIKE operator.

WHERE StartDate > '20040701' AND ModifiedDate LIKE 'E%'

Key Columns

It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. However, the query optimizer is more likely to choose a filtered index if it covers the query. For more information about covering queries, see Creating Indexes with Included Columns.

In some cases, a filtered index covers the query without including the columns in the filtered index expression as key or included columns in the filtered index definition. The following guidelines explain when a column in the filtered index expression should be a key or included column in the filtered index definition. The examples refer to the filtered index, FIBillOfMaterialsWithEndDate that was created previously.

A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results. For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results. FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression. For example, FIBillOfMaterialsWithEndDate is valid for the following query because it selects a subset of rows from the filtered index. However, it does not cover the following query because EndDate is used in the comparison EndDate > '20040101', which is not equivalent to the filtered index expression. The query processor cannot execute this query without looking up the values of EndDate. Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';
GO

A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set. For example, FIBillOfMaterialsWithEndDate does not cover the following query because it returns the EndDate column in the query results. Therefore, EndDate should be a key or included column in the filtered index definition.

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

The clustered index key of the table does not need to be a key or included column in the filtered index definition. The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

Data Conversion Operators in the Filter Predicate

If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

The following example creates a table with a variety of data types.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO

In the following filtered index definition, column b is implicitly converted to an integer data type for the purpose of comparing it to the constant 1. This generates error message 10611 because the conversion occurs on the left hand side of the operator in the filtered predicate.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

The solution is to convert the constant on the right hand side to be of the same type as column b, as seen in the following example:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

Moving the data conversion from the left side to the right side of a comparison operator might change the meaning of the conversion. In the above example, when the CONVERT operator was added to the right side, the comparison changed from an integer comparison to a varbinary comparison.

Referencing Dependencies

The sys.sql_expression_dependencies catalog view tracks each column in the filtered index expression as a referencing dependency. You cannot drop, rename, or alter the definition of a table column that is defined in a filtered index expression.

When to Use Filtered Indexes

Filtered indexes are useful when columns contain well-defined subsets of data that queries reference in SELECT statements. Examples are:

• Sparse columns that contain only a few non-NULL values.

• Heterogeneous columns that contain categories of data.

• Columns that contain ranges of values such as dollar amounts, time, and dates.

• Table partitions that are defined by simple comparison logic for column values.

Reduced maintenance costs for filtered indexes are most noticeable when the number of rows in the index is small compared with a full-table index. If the filtered index includes most of the rows in the table, it could cost more to maintain than a full-table index. In this case, you should use a full-table index instead of a filtered index.

Filtered indexes are defined on one table and only support simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view.

Filtered Index Feature Support

In general, the Database Engine and tools provide the same support for filtered indexes that they provide for nonclustered full-table indexes, considering filtered indexes as a special type of nonclustered indexes. The following list provides notes about tools and features that fully support, do not support, or have restricted support for filtered indexes.

• ALTER INDEX supports filtered indexes. To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.

• The missing indexes feature does not suggest filtered indexes.

• The Database Engine Tuning Advisor considers filtered indexes when recommending index tuning advice and can recommend an is not null filtered index.

• Online index operations support filtered indexes.

• Table hints support filtered indexes, but have some restrictions that do not apply to non-filtered indexes. These are explained in the following section.

Query Considerations

The query optimizer can use a filtered index if the query selects the same results with and without using the filtered index. The filtered index FIBillOfMaterialsWithEndDate described previously is valid for the following two queries. In the first example, the query predicate is an exact match to the filtered index predicate, WHERE EndDate IS NOT NULL. In the second example, the query predicate is more selective than the filter predicate because it contains a subset of rows in the index.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate < '20040701';
GO

The next query can also use FIBillOfMaterialsWithEndDate. However, the optimizer might not select the filtered index because of other factors that determine the query cost such as the selectivity of the query predicate. You can force the optimizer to choose the filtered index by using it as a query hint as shown in the following example.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IN ('20040825', '20040908', '20040918');
GO

The query optimizer will not use a filtered index if the query can return rows that are not in the filtered index. For example, the query optimizer will not consider FIBillOfMaterialsWithEndDate for the following query because it is possible for the query to return a row with a NULL EndDate and a non-null ModifiedDate, which cannot be in the FIBillOfMaterialsWithEndDate because it only contains non-NULL values for EndDate.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO

If a filtered index is used explicitly as a table hint and the filtered index might not contain all of the query results, the query optimizer generates query compilation error 8622. In the following example, the query optimizer generates error 8622 because FIBillOfMaterialsWithEndDate is not valid for the query and it is explicitly used as an index hint:

SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO

Parameterized Queries

In some cases, a parameterized query does not contain enough information at compile time for the query optimizer to choose a filtered index. It might be possible to rewrite the query to provide the missing information. In the following example, the query optimizer does not consider the filtered index FIBillOfMaterialsWithComponentID for the SELECT statement because the parameter values for @p and @q are not known at compile time. The query example below runs with SHOWPLAN_XML set to ON so that you can view the unmatched filtered indexes for parameterized queries in the SHOWPLAN_XML output.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials 
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO

The UnmatchedIndexes element and Parameterization subelement in the SHOWPLAN_XML output indicate that the filtered index was not a match for the query. For information about how to view the SHOWPLAN_XML output, see XML Showplans.

The solution is to modify the query so that the query results are empty when a parameterized expression is not a subset of the filter predicate. The following query demonstrates this modification. By adding the ComponentID in (533, 324, 753) expression to the WHERE clause, the results of the query are guaranteed to be a subset of the filtered predicate expression. With this modification, the query optimizer can consider the filtered index FIBillOfMaterialsWithComponentID for the following SELECT statement.

USE AdventureWorks2008R2;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

Simple Parameterization

In most cases, the query optimizer will not perform simple parameterization (referred to as "auto-parameterization" in SQL Server 2005) on a query if the query plan includes a filtered index. Performing simple parameterization on such queries can broaden the range of possible parameter values so that the filtered index does not guarantee accuracy for the query results. For example, the query optimizer might not perform simple parameterization if the WHERE clause of the SELECT statement uses a column that is used in the predicate of a filtered index, because it is likely that the query plan will include a filtered index.

When appropriate, you might be able to parameterize the query by rewriting it, using the guidelines described in this section to ensure the filtered index will cover the query.

Queries with Key Lookups

The query optimizer can use a filtered index, even though it does not cover the query, by performing a key lookup to retrieve the residual columns that the filtered index does not cover. For more information about key lookups, see Key Lookup Showplan Operator. The query optimizer might choose this approach if the estimated number of key lookups is small. The following query uses an index hint to force the query processor to use FIBillOfMaterialsWithEndDate with bookmark lookups for EndDate. The key lookup occurs for the EndDate > @date comparison in the query predicate.

USE AdventureWorks2008R2;
GO
DECLARE @date AS DATE;
SET @date = '20040825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

Notice that EndDate > @Date is not an exact match with the filtered index expression EndDate IS NOT NULL. The filtered index is still valid for this parameterized query because it returns a subset of the rows defined by the filtered index expression.

See Also

Reference

CREATE INDEX (Transact-SQL)

Concepts

Using Statistics to Improve Query Performance