on March 2, 2020
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.
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.