SQL Server - Indexed Views

最新推荐文章于 2025-11-26 14:12:29 发布

原创最新推荐文章于 2025-11-26 14:12:29 发布 · 166 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

SQL Server 专栏收录该内容

7 篇文章

订阅专栏

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

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