Press "Enter" to skip to content

Blocking during Creation of Indexed Views

Tom Zika runs into a problem:

There was a need to make changes to a table with an Indexed View. Since Indexed Views must be created with SCHEMABINDING, the View must be dropped and recreated.

From past experience, I knew that this operation blocked all queries (Read/Write) that referenced any table from the View’s definition for the duration of the Clustered index creation, even under the RCSI level.

Because the index might be large and the maintenance window small, I want to do that as fast as possible.

Tom does a good job of taking us through the problem as well as the solution. Definitely worth a read if you’re creating indexed views with Enterprise Edition.