Filtered Index in SQL Server 2008

Filtered Index is a new feature in SQL SERVER 2008. Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.


When we see an Index created with some WHERE clause then that is actually a FILTERED INDEX.


For Example:
CREATE NONCLUSTERED INDEX NCI_Event
ON DatabaseLog(Event)
WHERE Event= 'CREATE_TABLE'


Points to remember when creating Filtered Index:

• They can be created only as Nonclustered Index
• They can be used on Views only if they are persisted views.
• They cannot be created on full-text Indexes.

Execute the below query and see the performance impact of filtered index on Execution Plan.


Query1: Select * from DatabaseLog where Event='CREATE_TABLE'
Query2: Select * from DatabaseLog where Event='ALTER_TABLE'


Query1 is using a Filtered index and it's Query Cost is 29%. Query2 is not using Filtered Index and it's cost is 71%. See below execution plan for reference.

Conclusion:

A filtered Index is an optimized non clustered Index which is one of the great performance improvements in SQL SERVER 2008 reducing the Index storage cost and reduces maintenance cost.