Exploring SQL Server 2008’s Filtered Indexes

One of the most interesting features in SQL Server 2008 is filtered indexes. Let’s start with a quick explanation and then dig into the details and results.

A filtered index is simply an index with a WHERE clause. For example, if you have a table where a date is NULL in 90% of the records but you need to be able to find only records with a non-NULL date, a filtered index will have only the 10% that are not NULL.

It can be very useful for saving space, saving index maintenance costs, and making queries run faster. However, it is important to understand under whatcircumstances it will make your query faster.