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: 

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.

No Comments