SQL Server

网络浪子

　　
　　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