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

Question

Hi folks,

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.

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.