December 22, 2010

Filtered Index in SQL Server 2008

Filtered indexes are a new feature of SQL Server 2008. They are optimized non-clustered indexes that can be created on a subset of rows.

When can Filtered Index be used?

Let us say you have a large table that contains order details, however the most frequently run query on that table is for all orders belonging to the year 2010. In this case, you can create a filtered index that includes only dates of the year 2010.

Another scenario is if you have a table that contains order details and want to index only those rows whose Shipping Status is ‘Pending’ or if a table contains a large amount of null values but you want to query only the non-null values in that table. In such a case, creating a filtered index that fetches non-null values will improve query performance.

Understanding your tables and data is a key requirement to building efficient filtered indexes. Use the SQL Server Profiler to help you determine which columns or category or type of data is queried the most, and then take a decision accordingly.

Note: Filtered Indexes cannot be created on Views.

Advantages of Filtered Index

Here are some advantages of using Filtered Indexes

  • Allows you to create an index on a subset of data
  • Improves data storage by using less disk space for storing the index
  • Reduces time taken to rebuild index
  • Reduces time taken to look for data and thus increases the query performance and execution plan quality
  • Reduces index maintenance overhead
  • Filtered indexes lead to filtered statistics which cover only the rows in the
    filtered index. Thus they are more efficient than full table statistics.

Creating a Filtered Index

Filtered Index can be created by adding the WHERE clause to the normal CREATE INDEX statement. Here’s how to create a Filtered Index on the Northwind Orders table for orders placed since 1/1/1998


Once the filtered index is created, you can fire queries and look at the execution plan to see if the filtered index is used. Note that even when a query qualifies the filtered index criteria, SQL Server may not choose to use the filtered index if the I/O cost of using a Filtered index, is more than the I/O cost of performing a Clustered index scan.

In order to modify a filtered index expression, just use the CREATE INDEX WITH DROP_EXISTING query.

Note: Also make sure to check my article Filtered Index Vs Indexed Views in SQL Server 2008

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

1 comment:

len said...

nice article suprotim. I visit this site couple of time every week and it's worth it everytime!