SQL Server 2008 Filtered Indexes

When a new version of SQL Server arrives we particularly like to look at the new performance tuning features of the product and analyse it and think where it sits in the past, present and future scenarios.

Filtered Indexes allow you create sub-indexes based on a limited amount of data.  With a normal index the index is on every single row.  Succintly, a filtered index is like a WHERE clause for indexes.  The benefits are that its much smaller than an all encompassing index.

Unfortunately FIs are not picked up by the Missing Indexes Feature.  They are only picked up by the DETA (Database Tuning Advisor).  It might be worthwhile to run the DETA to pick these up.

It may be benefical to add FIs to

Columns with a large proportion of nulls to data
Static tables or Lookup tables with a disproportionate distribution of data
Data Warehouses Fact or Dimension tables
A dynamic situation such as the last two weeks worth of data.  If your application is only focussed on the immediate data, create a filtered index on the date of the last two weeks, you could drop it nightly and reinstate it.  Or create a new one and drop the old.