Menu
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.
331
Some reasons for dropping an index include: The index is no longer required. The index is not providing anticipated performance improvements for queries issued against the associated table. For example, the table might be very small, or there might be many rows in the table but very few index entries.
Indexes can only be created on views which have the same owner as the referenced table or tables. This is also called an intact ownership-chain between the view and the table(s). Typically, when table and view reside within the same schema, the same schema-owner applies to all objects within the schema.
Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution.
Indexes and constraints will slow inserts because the cost of checking and maintaining those isn't free. The overhead can only be determined with isolated performance testing.
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.
51
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
