Filtered Indexes v Indexed Views

I was just watching a TechEd Online video on SQL 2008 Q&A and learnt a neat difference between filtered indexes and indexed views.

Indexed Views and Filtered Indexes provide similar functionality. The main differences are

Indexed views
1. provide more complex filters and also a different shape of data, i.e. include a group by.
2. are only considered with Enterprise Edition (without using NOEXPAND hint)
3. are NOT automatically considered, the query has to go through a number of levels of query optimisation before indexed views are considered
4.are seperate entities, if your indexed view doesn't cover all the columns you have to join back to the source table to get the additional columns

Filtered Indexes
1. Only allow a simple set of filters
2. Are considered with ALL editions
3. Are considered earlier in the optimisation stack than in Indexed Views even for trivial plans (very simple ones)
4. Are just like a normal index and so still have reference to source table. So if index isn't a covering one the query op can perform a lookup to the base table to get the other data

-
Published 13 May 2009 21:36 by simonsabin

Comments

14 May 2009 08:50 by tonyrogerson

# re: Filtered Indexes v Indexed Views

Dont forget the most important part and the problem with index views - concurrency (locking)! That doesn't suffer with filtered indexes.

Tony.

14 May 2009 09:23 by GrumpyOldDBA

# re: Filtered Indexes v Indexed Views

Having made use of both there is one critical point about filtered indexes that if you don't include the filter in your query the filtered index is ignored, or it was prior to SP1 anyway.

There's also the point that an indexed view allows you to create multi table indexes ( in effect ) something a filtered index doesn't.

I have to be honest I wouldn't have considered the two as being similar at all, and we know Tony is biased against indexed views < grin >