Press "Enter" to skip to content

Altering an Indexed View Drops All of Its Indexes

Kendra Little has some thoughts on indexed views:

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.)

Click through for the answer.