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