All geek questions in one place

Sql Server Index

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

I have an indexed view called

AssignmentDetail

when I look at the execution plan for

select * from AssignmentDetail

it shows the execution plan of all the base indexes of all other tables that the indexed view should abstract.

I would think that the execution plan would be just a clustered index scan of PK_AssignmentDetail (the name of the clustered index for my view), but it is not.

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

Any insight would be appreciated.

+4
source share
2 answers

The enterprise version of SQL Server is smart enough to search and use indexed views when they exist. However, if you are not using the Enterprise edition, you need to explicitly tell it to use an indexed view, for example:

 select * from AssignmentDetail WITH (NOEXPAND)
+5
source

The indexed view point is not acceleration

 SELECT * FROM MyView

The thing that will help you improve performance is the index in the column of the view itself, for example.

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

That way you can have an index in ViewColumnA and ViewColumnB, which can exist in different tables.

+2
source

Source: https://habr.com/ru/post/1309124/

More articles:


All Articles