Filtered Index Vs Indexed Views in SQL Server 2008

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 Server 2008, but only be used in the Developer, Enterprise and DataCenter Editions. For other editions, you have to use ‘NOEXPAND’ option to explicitly tell SQL Server to use the indexed view
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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook