According to BOL, “ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view.”
We can see this behavior by creating an indexed view using the Northwind database. The script is shown below:
CREATE VIEW [dbo].[MyView] WITH SCHEMABINDING
SELECT ContactName, CompanyName
CREATE UNIQUE CLUSTERED INDEX [inx_MyIndex] ON [dbo].[MyView]
Note that the indexed view must be created with the SCHEMABINDING option. SCHEMABINDING binds the view to the schema of the underlying base tables.
Now the fun part. We alter the index view by adding a new column “ContactTitle”, like so:
ALTER VIEW [dbo].[MyView] WITH SCHEMABINDING
SELECT ContactName, CompanyName, ContactTitle
-- Output from Management Studio
-- Command(s) completed successfully.
By looking at the view in management studio, after refreshing the tree, we see that the index is no longer there! I expected to see a warning message when this occurred :-(
The easiest way to work around this is to make a copy of the index creation script before you alter the indexed view and run it afterwards.