December 28, 2010

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 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, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

No comments: