Indexed views are views whose results are persisted in the database and indexed for fast access. Indexed views are supported when your Microsoft Access project is connected to Microsoft SQL Server 2000 Enterprise Edition or SQL Server 2000 Developer Edition, but they are not supported in Microsoft SQL Server 2000 Desktop Edition. Indexed views are best used when data is read-only (such as a decision support system), queries of the indexed view do not involve aggregates or joins, and the base table schema definitions of the indexed view are not likely to change. For more information on indexed views, see the SQL Server documentation.
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 Query 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.
What happens when you modify a base table or column
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 does one or more of the following:
- Deletes the base table
- Renames the base table
- Recreates the base table
- Removes a column from the base table and the view includes that column
- Renames a column from the base table and the view includes that column