Altering Indexed Views - Gotcha
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
AS
SELECT ContactName, CompanyName
FROM dbo.Customers
GO
CREATE UNIQUE CLUSTERED INDEX [inx_MyIndex] ON [dbo].[MyView]
(
[ContactName] ASC
)
GO
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:
Now the fun part. We alter the index view by adding a new column “ContactTitle”, like so:
ALTER VIEW [dbo].[MyView] WITH SCHEMABINDING
AS
SELECT ContactName, CompanyName, ContactTitle
FROM dbo.Customers
GO
-- 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.