indexed view

I have an indexed view that uses two tables.  i have 10 non-clustered indexes on this view to support search functionality for 10 columns. The tables are high traffic oltp tables. For my query i get 500,000 rows after join and where conditions. I see good performance gains on this one. However i have below qtn.

whenever there is an insert/update into the underlying tables which happens often will the indexes be created for each insert/update? if yes,

 a) will it be partial update or full update ?
b) Will the index be updated during insertion/update into underlying tables or while selecting from view the next time after insert/update?
JyozealAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duy PhamFreelance IT ConsultantCommented:
As I understand, indices in Views are the same as in Tables (except that the first index of a View must be a clustered unique index). And whenever there is an insert/update action happening on table included in the View, indices will be updated (not rebuilt), and after a while (couple of weeks or even a few days), indices are fragmented, and they should be rebuilt (or defrag).
So in my opinions, answers to your questions should be:
1. Yes, but partial update only
2. Sure it will be updated and ready for query right after an insert/update action.

Just my 2 cents.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you have a regular maintenance plan for rebuild and reorganize indexes then you shouldn't worry with this.
It's like you're working with indexes on tables. No big difference.
0
JyozealAuthor Commented:
Thanks. This helps.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.