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 thefiltered 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.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.