SQL Server - Indexed Views-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_21322/article/details/82577662

本文介绍了如何使用'SCHEMABINDING'创建SQL Server中的视图，并详细解释了创建带有集群索引的视图所需的步骤及约束条件。此外，还深入探讨了'SCHEMABINDING'的作用及其对底层表的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. Create the view with 'SCHEMABINDING'

CREATE VIEW [dbo].[vTest] WITH SCHEMABINDING AS
SELECT

2. Create the index

CREATE UNIQUE CLUSTERED INDEX idx_vTest ON vTest(IndexColumn)

3. Constrains:

The view must have been created with certain SET options, such as QUOTED_IDENTIFIER and CONCAT_NULL_YIELDS_NULL set to ON.
The session creating the index must also have the correct SET options.
Any user-defined function referenced by the view must have been created using WITH SCHEMABINDING.
The view must be deterministic (consistently providing the same result given the same input).
The base tables must have been created with the proper ANSI_NULLS setting.
The result set of the view is physically stored in the database, thus storage space for the clustered index is also a constraint to consider.

4. What is 'SCHEMABINDING'?

To put it simply, once you create a view with schemabinding, you cannot change the underlying tables in a way that would break the view. Examples of this would be removing columns or dropping tables that are specified in the view.

5. Reference:

http://www.codeproject.com/Articles/199058/SQL-Server-Indexed-Views-Speed-Up-Your-Select-Quer

http://strangenut.com/blogs/dacrowlah/archive/2008/12/02/what-does-schemabinding-mean-in-sql-2005-and-2008.aspx