Sql server indexed view

Question

OK, I'm confused about sql server indexed views(using 2008)

I've got an indexed view called

AssignmentDetail

when I look at the execution plan for

select * from AssignmentDetail

it shows the execution plan of all the underlying indexes of all the other tables that the indexed view is supposed to abstract away.

I would think that the execution plan woul simply be an clustered index scan of PK_AssignmentDetail(the name of the clustered index for my view) but it doesn't.

There seems to be no performance gain with this indexed view what am I supposed to do? Should I also create a non-clustered index with all of the columns so that it doesn't have to hit all the other indexes?

Any insight would be greatly appreciated

Answer 1

The Enterprise edition of SQL Server is smart enough to look for and make use of indexed views when they exist. However, if you are not running Enterprise edition, you'll need to explicitly tell it to use the indexed view, like this:

select * 
from AssignmentDetail WITH (NOEXPAND)

Answer 2

The point of an indexed view is not to speed up

SELECT * FROM MyView

The thing it will help you increase performance is an index on a column of the view itself, such as.

SELECT * FROM MyView WHERE ViewColumnA = 'A' and ViewColumnB = 'B'

You can therefore have an index on ViewColumnA and ViewColumnB which could actually exist on different tables.