A couple of days ago, I had written an article on Filtered Index in SQL Server 2008. If you ever desired to create an index only on some rows, make sure you read about filtered indexes.
A SqlServerCurry.com reader ‘Shogunpoma’ sometime back had written to me asking if Filtered indexes were a better option than Indexed Views. I have listed some differences between Filtered Index and Indexed View that will help understand the differences between the two:
|Filtered Index||Indexed Views|
Created on one or more columns of one table
You can index across a view containing multiple tables
Can be created and used in any edition of SQL Server
Can be created in all editions of SQL
You can create Non-unique Filtered Indexes
You can create only Unique Indexed Views
Reduced index maintenance costs. The query processor uses fewer CPU resources to update a filtered index
Since Indexed Views are more complex, the index can be larger and will consume more CPU resources while updating an Indexed View.
You can only use simple operators (IS IS NOT = <> != > >= !> < <= !<)) in the WHERE clause while creating Filtered Index. Clauses such as ‘LIKE’ cannot be used
No such limitation
You can do online index rebuilds for filtered indexes
Online index rebuilds are not supported for indexed views
Better query compilation of Filtered Index over Indexed View
The query compilation of Indexed Views is not as good as compared to Filtered Index. Query optimizer uses a filtered index in many situations than the equivalent indexed view.
For the reasons listed above, I would recommend using a filtered index instead of an indexed view, depending on your requirement and whenever possible.
References: Filtered Index Design Guidelines