Filtered Indexes in SQL Server 2008

A new feature in SQL Server 2008 (which has not been well publicized) is filtered indexes. SQL Server Books Online (BOL) describes it this way:

A filtered index is an optimized nonclustered index, suited for queries that select a small percentage of rows from a table. It uses a filter predicate to index a portion of the data in the table. A well-designed filtered index can improve query performance, reduce storage costs, and reduce maintenance costs.

-- Sample Filtered Index for AdventureWorks
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
ON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL;

SQL Server BOL has this to to say about filtered indexes:

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.
Technorati Tags:

 

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s