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
ON
(
SalesOrderID ASC,
ProductID ASC
)
WHERE (ModifiedDate < '03/01/2004')
Posts
