Monday, November 14, 2011

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:
ON DatabaseLog(Event)

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.

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.

No comments: