Indexed Views

SQL Server 2000 supports indexed views.  Indexed views are views whose results are persisted in the database and indexed for fast access.

To learn how to create an indexed view, see Creating Indexed Views.

As with any other views, indexed views depend on base tables for their data.  Such dependency means that if you change a base table contributing to an indexed view, the indexed view might become invalid.  For example, renaming a column that contributes to a view invalidates the view.  To prevent such problems, SQL Server supports creating views with "schema binding."  Schema binding prohibits any table or column modification that would invalidate the view.  Any indexed view you create with the View Designer automatically gets schema binding, because SQL Server requires that indexed views have schema binding.

Schema binding does not mean you cannot modify the view; it means you cannot modify the underlying tables or views in ways that would change the view's result set.

Using the Table Designer or Database Designer, you might attempt to modify a base table or column that contributes to a view defined with schema binding.  If your attempted modification could invalidate the view, the Designer warns you and asks you whether you want to proceed.  If you choose to proceed, these things happen:

  • Your modifications to the base table occur.

  • All views depending on the base table views are changed so that "schema binding" is removed.  Thus, your subsequent changes to the base table will proceed without warning.

  • If the dependent views were indexed, the indexes are deleted.

The Table Designer and Database Designer warn you before modifying a base table only if that base table contributes to a view with schema binding and if your modification satisfies one or more of the following conditions:

  • Your modification deletes the base table.

  • Your modification renames the base table.

  • Your modification recreates the base table.

  • Your modification removes a column from the base table and the view includes that column.

  • Your modification renames a column from the base table and the view includes that column.

See Also

Creating Indexed Views