Before creating view you should ensure:

1. Verify the SET options are correct for all existing tables that will be referenced in the view. SET options, such as QUOTEDIDENTIFIER, ANSINULLS, and CONCATNULLYIELDS_NULL set to ON.
2. The session creating the index must also have the correct SET options (as listed above).

3. The view must be defined with SCHEMABINDING to create an index on it.

   CREATE VIEW VW_Easy123 WITH SCHEMABINDING AS Select PhoneNo from MyTable

   GO

   CREATE unique clustered index IXVWEasy123PhoneNo on VWEasy123(PhoneNo).

4. You can not use '*' in the select statement of a view when it is schema bound. Explicit list of column(s) should be specified

Some other possible scenario:

• Only table owner can create view, and after that - index on the view, so if it is under table owner account - this will fail

• Clustered index on a view must be unique, so if there are duplicated phone numbers in [myTable] - it will fail. That may be resolved by:

  • Adding more column(s) to the SELECT list and index specification - untill unique criteria is met (it is another thing using more columns for clustered index is not the best approach)

  • Choosing another column for clustered index is a better approach.

• The compatibility level of the database cannot be less than 80

Cannot create index on view THAT contains text, ntext, image, FILESTREAM or xml columns.

1. To create a unique clustered index on view, The view must be created using schema binding. The SELECT statement in the view cannot contain these Transact-SQL syntax elements: * the select list cannot use the * or table_name.* syntax to specify columns. Column names must be explicitly stated binding.

2. Phone number in the underlying table should be unique. If duplicate entry exists it will not allow creating unique clustered index on view.

3. The CREATE INDEX statement must meet these requirements in addition to the normal CREATE INDEX requirements: a) The user executing the CREATE INDEX statement must be the view owner. b) These SET options must be set to ON when the CREATE INDEX statement is executed: i. ANSINULLS ii. ANSIPADDING iii. ANSIWARNINGS iv. ARITHABORT v. CONCATNULLYIELDSNULL vi. QUOTEDIDENTIFIERS c) The NUMERICROUNDABORT option must be set to OFF. d) The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.

1. You need to specify “WITH SCHEMABINDING” in the view definition. You can specify it while creating the view or while altering the view. i.e. Either with CREATE VIEW statement or with ALTER VIEW statement.
2. You must provide the fully qualified name of the table. It should be in the format [schema-name].[table-name]      e.g. dbo.MyTable
3. You can’t use SELECT * to specify the fields. You must specify the field names explicitly.

So if you modify your sql statements like this, it should work.

CREATE VIEW VW_Easy123 WITH SCHEMABINDING
AS
Select ID,PhoneNo from dbo.MyTable
GO
create unique clustered index IX_VW_Easy123_PhoneNo on VW_Easy123(PhoneNo)
GO

As per BOL (reduced to the statements specifically related to the question) a view must meet the following requirements before you can create a clustered index on it:

• The ANSINULLS and QUOTEDIDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed.
• The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
• The base table referenced by the view must have the same owner as the view.
• The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base table.
• User-defined functions referenced in the view must have been created with the SCHEMABINDING option.
• Table must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.
• All functions referenced by expressions in the view must be deterministic.
• The * or table_name.* syntax to specify columns. Column names must be explicitly stated.

Regarding the CREATE INDEX statement the following rules apply:

• The user that executes the CREATE INDEX statement must be the view owner.
• The following SET options must be set to ON when the CREATE INDEX statement is executed: ANSINULLS, ANSIPADDING, ANSIWARNINGS, CONCATNULLYIELDSNULL, QUOTED_IDENTIFIER
• The NUMERIC_ROUNDABORT option must be set to OFF. This is the default setting.
• When you create a clustered or nonclustered index, the IGNOREDUPKEY option must be set to OFF (the default setting).
• The view cannot include text, ntext, or image columns, even if they are not referenced in the CREATE INDEX statement.
• An imprecise expression that appears as the value of an index key column must be a reference to a stored column in a base table underlying the view. This column may be a regular stored column, or a persisted computed column. No other imprecise expressions can be part of the key column of an indexed view.

Additionally, column PhoneNo must not contain duplicate values.

And, of course, the database in question needs to be at least in comp level 80.

This fails because, we are trying to create an index on a view, i.e. creating indexed view. This means that storing the schema of the table/view in the memory. So with out schema bound, it is not possible maintain the base table without any changes. To store the original schema of the base table when the view is created, it is necessary to create a view which has schemabinding. The above creation of index is possible with schemabinding option:

Alter VIEW VWEasy123 With Schemabinding AS Select,,...., from dbo.MyTable GO create unique clustered index IXVWEasy123PhoneNo on VW_Easy123(PhoneNo)

According to this link of bookonline, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f86dd29f-52dd-44a9-91ac-1eb305c1ca8d.htm there are lots of things you should cosider to create an index view. If we can be sure about the sets, and just see the command you have written, it seems that there are two main errors: 1. not binding 2. using '*' in the select statement

coz its not schema bound.If we need to create an index on a view first it has be schema bound

This fails because, we are trying to create an index on a view, i.e. creating indexed view. This means that storing the schema of the table/view in the memory. So with out schema bound, it is not possible maintain the base table without any changes. To store the original schema of the base table when the view is created, it is necessary to create a view which has schemabinding. The above creation of index is possible with schemabinding option:

Alter VIEW VWEasy123 With Schemabinding AS Select,,...., from dbo.MyTable GO create unique clustered index IXVWEasy123PhoneNo on VW_Easy123(PhoneNo)

SQLServer Quiz 2011 - Why can't I Create this View?

Answer:

Robert, this is really good and practical question.

This fails because we are trying to create an index on a view, VW_Easy123, i.e., creating indexed view. When we try to execute this query we get following error:

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'VW_Easy123' because the view is not schema bound.

This error clearly suggested that in order to create an index on this view we have to make it schema bound. Schema binding ties an object to the base object that this new object depends upon. So without schema binding if a view is created and the underlying table is changed, the view may break, but the table change can still occur. With schema binding, if the base object is bound to another object, you will not be able to modify the based object unless you drop or alter the object to remove the schema binding.

This means we cannot create index on a view since schema is not bound, therefore in order to store the original schema of the base table when the view is created, it is necessary to create a view which has schema binding option. The above creation of index is possible with schema binding option as shown below:

There are three steps to make it work perfect, and they are.

Step 1: We have to add WITH SCHEMABINDING on CREATE VIEW statement to make it work.

Step 2: We have to add the list of columns in SELECT list, and SELECT * from MyTable cannot be used while creating views. Column names must be explicitly stated

Step 3: Table name (MyTable) needs to be referenced by two-part names in the view, like dbo.myTable since one-part names are not allowed in views.

We can change the above query as shown below to make it work by incorporating above steps in the query.

CREATE VIEW VW_Easy123 WITH SCHEMABINDING 
AS
SELECT [empID]
      ,[empSalary]
      ,[empFName]
      ,[empLName]
      ,[createdDt]
      ,[PhoneNo]
  FROM dbo.MyTable
    GO
create unique clustered index IX_VW_Easy123_PhoneNo on VW_Easy123(PhoneNo)

Based on BOL, there are rules on creating index on Views and they are listed below.

A view must meet the following requirements before you can create a clustered index on it:

• The ANSINULLS and QUOTEDIDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed.
• The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
• The view must not reference any other views, only base tables.
• All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
• The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.
• User-defined functions referenced in the view must have been created with the SCHEMABINDING option.
• Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.
• All functions referenced by expressions in the view must be deterministic.
• Implicit conversion of non-Unicode character data between collations is also considered nondeterministic, unless the compatibility level is set to 80 or earlier.
• Creating indexes on views that contain these expressions is not allowed in 90 compatibility mode. However, existing views that contain these expressions from an upgraded database are maintainable. If you use indexed views that contain implicit string to date conversions, be certain that the LANGUAGE and DATEFORMAT settings are consistent in your databases and applications to avoid possible indexed view corruption.
• If the view definition uses an aggregate function, the SELECT list must also include COUNT_BIG (*).
• The data access property of a user-defined function must be NO SQL, and external access property must be NO.
• Common language runtime (CLR) functions can appear in the select list of the view, but cannot be part of the definition of the clustered index key. CLR functions cannot appear in the WHERE clause of the view or the ON clause of a JOIN operation in the view.
• The SELECT statement in the view cannot contain the following Transact-SQL syntax elements: The * or table_name.* syntax to specify columns. Column names must be explicitly stated.

If we follow these index creation guidelines carefully, we should be able to create above clustered index on a view.

Thanks,

Abi Chapagai