Filtered Indexes in SQL Server 2008

Filtered Indexes in SQL Server 2008

SQL Server 2008 introduces Filtered Indexes which is an index with a WHERE clause. Doesn’t it sound awesome especially for a table that has huge amount of data and you often select only a subset of that data? For example, you have a lot of NULL values in a column and you want to retrieve records with only non-NULL values or in another scenario you have several categories of data in a particular column, but you often retrieve data only for a particular category value. This article talks about what a Filtered Index is, how it differs from other indexes, its usage scenario, its benefits and limitations.