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 AdventureWorks 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 AdventureWorks;
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).

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 AdventureWorks;
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

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.

*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 AdventureWorks;
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 thanSeptember 1, 2000, 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 > '20000901';
GO

In the next example, the query selects start dates greater than June 1, 2000 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 > '20000601';
GO

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 > '20000701' AND ModifiedDate LIKE 'E%'

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 > '20000101', 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 > '20000101';
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 primary key of the table does not need to be a key or included column in the filtered index definition. The primary key is automatically included in all nonclustered indexes, including filtered indexes.

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

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.

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.

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.
  

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 AdventureWorks;
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 AdventureWorks;
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

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 AdventureWorks;
GO
DECLARE @date AS DATE;
SET @date = '20000825'
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.