SQL Server
1. Create the view with 'SCHEMABINDING'
CREATE VIEW . 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
页:
[1]