Altering an indexed view in SQL Server drops all indexes

I learned an interesting thing about ALTER VIEW behavior in SQL Server when applied to indexed views. This is covered in the product documentation, but it’s not something I would have expected:

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

ALTER VIEW documentation

When I first heard about this behavior, it sounded like a bug to me: why should an alteration like adding a column to a view remove all the indexes?

A colleague of mine at Redgate wondered: is the behavior the same with the new CREATE OR ALTER syntax as it is with just plain ALTER? (Spoiler: I tested and it is the same: CREATE OR ALTER also drops all indexes on the view.)

One note: if you are using Redgate tooling to commit your database objects to version control, never fear: it handles this for you and will automatically put the indexes back on your indexed views if you alter them! This is true both for SQL Source Control and SQL Change Automation. Just be aware that behind the scenes the indexes must effectively be recreated to implement this change, so if large tables are involved in the view definition, deployment time will be impacted.

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 which requires a size-of-data operation 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.

After thinking about this, I can understand why if you allow ALTER VIEW, the simplest way to implement that against an indexed view is to make it the user’s problem to ensure that indexes can be re-created after altering a view.

However, I personally wonder if ALTER VIEW should even work for indexed views

This is one of those little niche behaviors that strikes me as problematic. From a user perspective, I’d personally rather that ALTER VIEW simply failed for indexed views and notified me that to change an indexed view, I need to drop the indexes, alter the view, then recreate the indexes which I need. Although that’s more work to me as a user, it helps me understand that this is not necessarily a cheap or low risk operation, and it reduces the risk of me accidentally deploying code that un-indexes a view which might be critical to performance.

However, this ship has sailed: the behavior is documented and established and unlikely to change in the SQL Server Engine anytime soon.

Want to see a demo?

Check out this fantastic video by Erik Darling where he discusses and demos this behavior.

4 Comments. Leave new

  • […] Kendra Little has some thoughts on indexed views: […]

    Reply
  • Alex Friedman
    March 3, 2020 11:01 pm

    Agreed, hidden side effects are the worst. As the Zen of Python goes, “explicit is better than implicit”.

    Reply
  • Thomas Franz
    March 10, 2020 2:02 am

    Regarding “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). ”

    When you add a new column to a table it does NOT write all the pages again, it just rembembers in the meta data, that there is a new column and what it initial default value was (usually NULL or when you defined a DEFAULT then this value). The new column will only really be written to the pages, when you update a record (or insert a new one of course). To be honest, I’m not sure, if the new column would be materialized, if you rebuild the clustered index.

    This behavior allows you add columns to very big tables mit terrabytes of data without any mentionable wait time (beside the time to get a table / schema lock).

    Reply
    • I should have said “when you add a column to a table which causes a size of data operation.” I’ll amend that now. The behavior kicks in depending on the nature of the column added and your version and edition.

      Reply

Leave a Reply to Thomas Franz Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu