Filtered Indexes

One of the cool new features of SQL 2008 is Filtered Indexes. A Filtered Index allows you to create an index on just a subset of data, using a filtering predicate. The reduction in index storage space can be significant.

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. A filtered index on only non-NULL values would therefore consume less then 10% of the original space of a regular index.

For a query like:

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

The following filtered index could be created:

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;

It’s good to realize that columns used in the predicate are not required to be included in the index itself. Notice that the index itself does not contain EndDate. It’s only included in the filter predicate. Because it’s included in the filter, the index will be used by the query.

Besides the reduction in storage space, a smaller index will also improve performance of queries, by requiring less reads and a lower cache memory footprint.

To see which indexes have filters defined, execute the following query:

SELECT name, filter_definition
FROM sys.indexes
WHERE  has_filter = 1

Note: Filtered indexes can, of course, only be applied to non-clustered indexes.

Share this