1

This is a follow up of ALTER VIEW drops Index from View question.

The accepted response indicates that ALTER VIEW will automatically drop any defined indexes.

This is somewhat counter intuitive to how other similar operations are happening:

  • it is natural to expect for related things such as permissions or indexes to be dropped on object drop not on alter
  • altering a table does not drop any index (actually you have to drop things like indexes, constraints etc.)

I am wondering about why having this behavior by default in the first place (i.e. is there any objective aspect like performance reduction in some cases due to view alteration).

Question: What is the rationale of dropping all indexes for an altered view?

Improve this question
4
  • 2
    Since the clustered index on an indexed view completely defines the data that’s in the view, altering an indexed view is analogous to dropping and re-creating a clustered index on a normal table, with the additional caveat that all the data disappears (so non-clustered indexes also get cleared out). Is this just academic? Are you using indexed views and this is a significant burden? Are you using indexed views for the right reason? (There are many reasons people try to use them, but most of them are weak reasons.)
    – Aaron Bertrand
    Commented Mar 1, 2020 at 20:21
  • Regarding the comparison to altering tables, you can't alter the definition of an indexed table column without first dropping the index.
    – Dan Guzman
    Commented Mar 1, 2020 at 21:01
  • 2
    @Alexei, you seem to realize that the index may be completely useless after altering the view. Either get rid of the indexes or forbid altering the view is a consequence (somewhat similar to cascade vs restrict for a foreign key). Why choose one over the other? I have no idea, I guess it is just one of those design decisions one has to make.
    – Lennart - Slava Ukraini
    Commented Mar 1, 2020 at 21:19
  • 1
    There's probably very few ALTER VIEW operations you could do where the existing index would be valid after the alteration (only things like changing ENCRYPTION). The design choice of doing it silently without forcing you to explicitly drop the index(es) first seems highly questionable to me though
    – Martin Smith
    Commented Mar 2, 2020 at 14:39

1 Answer 1

Reset to default
2

Kendra Little lays out some good theories for why this happens in her recent blog post: Altering an indexed view in SQL Server drops all indexes

This behavior makes some sense when I think about how indexed views are implemented

When you create an index on a view (we’re talking views using classic disk-based tables here), it materializes the view — in other words, it stores the data as if it is a table. That data is stored in rows on 8KB pages. These pages can fill up. So if you add a column to a view, just like adding a column to a table, the database engine needs to rewrite those pages to add the new column to each and every row (depending on datatypes and nullability, etc etc).

Also, it’s quite possible that when altering an indexed view, the alteration makes the view no longer adhere to the many rules required for indexed views.

Altering the view could completely change the structure of the data - number of columns, number of rows, the size of the rows, etc. So basically the table would need to be dropped and recreated anyway.

I find the second point, about how likely a change is to run afoul of the indexed view restrictions, to be particularly compelling.

Like Kendra mentions later, and others have mentioned in the comments here, it would be nice if one were simply blocked from altering indexed views. But that change will likely never happen due to backwards compatibility concerns.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.