Filter Index in SQL Server 2008...

Improve performance with Filtered Index

Filtered Index is used to index a portion of rows in a table. Filter index applies filters only on Non Clustered Index which improves query performance, reduce index maintenance costs, and reduce index storage costs compared to full-table indexes. It cannot be created on full-text Indexes

If an Indexes are created on WHERE Clauses then such Indexes are called as Filter Indexes. For E.g. We want to apply the Index to a Customer English Occupation where Occupation = 'Clerical', then We need to apply an Index on the Customer ID as given below

Select * from HumanResources Where Jobtitle = 'Marketing Manager'

Create NonClustered Index JobtitleNCI On HumanResources.Employee (BusinessEntityID)
Where JobTitle = 'Marketing Manager'

Select * from HumanResources Where Jobtitle = 'Marketing Manager'

Execute the Query by enabling the Actual Execution Plan [Press Control + M]

If you check the Execution plan query cost for the first plan was 19% and 3rd on with 14% . So after applying the Filter Index, performance of Query got improved. But there is a problem of Heap present in the 3rd plan as highlighted with Red color. It can be solved by applying a primary key to the table column.

 

Once you apply the Primary key as shown above, rerun the query to check whether the Heap changes to Look up

 

Now the Query has changed to Look up

 

Advantage of Filtered Index

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.

 

Regards

Daniel J

Dataware and Data Mining Architect...