Sql-server – the rationale of dropping all indexes for an altered view in SQL Server

indexmaterialized-viewsql serverview

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?

Best Answer

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.

Related Question