What's new in Sql Server 2008? (A string of articles by Irfan AQ)

Filtered Indexes (My Favourite!)

As the name shows, filtered indexes are criteria-based indexes. We specify the criteria as a WHERE clause similar to the WHERE clause in a simple SELECT statement. In fact a filtered index is an optimized non-clustered index. Also, clustered indexes cannot have a filter. If properly designed, filtered indexes enhance query performance to great extent. Randy Dyess (Introducing Filtered Indexes) has some interesting demonstration on how filtered indexes can make database developer’s life easy. I was reading another article on filtered indexes in which author was saying that with filtered indexes, days of over-indexing will be over.

Here is a simple example on creating filtered index:

USE AdventureWorks

CREATE NONCLUSTERED INDEX MyFilteredIndex
ON Sales.SalesOrderDetail
(
SalesOrderID ASC,
ProductID ASC
)
WHERE (ModifiedDate < '03/01/2004')