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 IndexIndexed 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

No comments: