Why do my indexes get dropped when I change an Index'd view's schema?

Question

Server: MS Sql Server 2008

When i create an indexed view .. and i then alter the view's schema, the index's all get dropped.

It's sooo annoying!

Can someone explain why this is? At first I thought that it could be because the fields the index requires are not in the schema any more (we did just alter it, right?) .... but for all the times when the index fields are in the view schema ... it should just leave the index there.

anyways.. rant rant rant ...

just hoping someone might have some inside knowledge on this.

Foreignism

Answer 1

The behavior is by design. From Books Online:

ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view.

When you modify the schema, the clustered index will have to be rebuilt. Since all non-clustered indexes rely on the clustered index, they have to be rebuilt. That's probably why all the indexes are dropped.

Rejoin

Yep. Agreed -- i was hoping to know WHY this was, by design. – Foreignism

Because the indexes have to be rebuilt. Automatically replacing the indexes could have an unexpected performance impact, is my guess, if you're talking about a lot of data. Therefore, you have the option of re-creating them immediately or at a time more convenient for other users of the system. – Rejoin

Ok - that makes a bit of sense. I wish they could say 'OK to recreate all indexes again?' or something ... kewl. that sates my desire to know what's going on. cheers! – Foreignism