Conditions for SQL Server to create an index on a view

SQL Server creates an index on a view the criteria for creating an index on a view requires three conditions: first, the view must be bound to the schema. To do this, you must add the with SCHEMABINDING in the CREATE VIEW statement, and if you are using Enterprise Manager, right-click on the space in the design interface and select bind to schema.
  Index must be a unique index.  www.2cto.com   To do this, you must specify UNIQUE in the CREATE INDEX. Third, the index must be a clustered index. To do this, you must specify CLUSTERED in the CREATE INDEX. Example: CREATE VIEW viewfoo with Schemabindingasselect id... create UNIQUE CLUSTERED INDEX index_viewfoo on Viewfoo (ID) &nb Requirements for the sp;*************************************************************** view   before creating a clustered index on a view, the view must meet the following requirements:      when executing   CREATE   VIEW   statements, the ANSI_NULLS   and   QUOTED_IDENTIFIER   options must be set to   on 。 The ObjectProperty   function reports this information for the view through   Execisansinullson   or   Execisquotedidenton   properties.     the ANSI_NULLS   option must be set to   on for all   create   table   statements to be created for the table referenced by the view. The     view cannot reference any other views, only the base table.     www.2cto.com   View all base tables referenced must be in the same database as the view, and the owner is also the same as the view.     You must create a view using the   SCHEMABINDING   option. SCHEMABINDING   Binds the view to the schema of the underlying base table.    you must have used the   SCHEMABINDING   option to create a user-defined function that is referenced in the view.     tables and user-defined functions must be made by the &nbsp 2   Part of the name reference. The names of   1   parts, 3   parts and   4   parts are not allowed. All functions referenced by an expression in the     view must be deterministic. The   isdeterministic   property of the ObjectProperty   function reports whether the user-defined function is deterministic. For more information, see deterministic functions and non-deterministic functions.   Select   Statements in     www.2cto.com   View cannot contain the following   Transact-SQL   syntax elements:    Select columns Table cannot specify columns using the   *   or   table_name.*   syntax. You must explicitly give the dequeue name.     cannot specify column names for tables that are used as simple expressions in multiple view columns. If all (or only one exception) references to a column are part of a complex expression or a parameter to a function, you can refer to the column more than once. For example, the following picklist is illegal:   select   ColumnA,   COLUMNB,   columna   execution   CREATE   INDEX The user of the   statement must be the owner of the view.    when executing   CREATE   INDEX   statements, the following   set   options must be set to   on:   ansi_nulls &nbsp ;   www.2cto.com  ansi_padding   ansi_warnings   arithabort   concat_null_ Yields_null   quoted_identifiers    the option   numeric_roundabort   option must be set to   OFF.  The   view cannot contain   text, ntext  , or   image   columns, even if they are not referenced in the   CREATE   INDEX   statement.    if the   SELECT   statement in the view definition specifies a   group   by   clause, the key for a unique clustered index can only be referenced in the   group   by & nbsp The column specified in the clause.

SQL Server criteria for creating indexes on a view