cannot create index on view because the view is not schema bound 2

[arkadia93]

How do you create an index on a view? I don't have experience of creating indexes on tables or views, and I am trying to create an index on a view but I am getting the error cannot create index because the view is not schema bound'...can anybody help?

 

[hmckillop]

here are the options for creating an indexed view.
As you can see the view must be created with the SCHEMABINDING option. SCHEMABINDING binds the view to the schema of the underlying base tables. This also applies to user-defined functions referenced in the view.

In order to do this use

CREATE VIEW vwMyView WITh SCHEMABINDING

" view must meet these requirements before you can create a clustered index on it:

The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.


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. SCHEMABINDING 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. One-part, three-part, and four-part names are not allowed.


All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports if a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.


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.


A table column name used as a simple expression cannot be specified in more than one view column. A column can be referenced multiple times provided all, or all but one, reference to the column is part of a complex expression or a parameter to a function. For example, this select list is invalid:
SELECT ColumnA, ColumnB, ColumnA

These select lists are valid:

SELECT ColumnA, AVG(ColumnA), ColumnA + Column B AS AddColAColB

SELECT SUM(ColumnA), ColumnA % ColumnB AS ModuloColAColB

A derived table.


Rowset functions.


UNION operator.


Subqueries.


Outer or self joins.


TOP clause.


ORDER BY clause.


DISTINCT keyword.


COUNT(*) (COUNT_BIG(*) is allowed.)


The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP are specified in queries referencing the indexed view, the optimizer can often calculate the needed result if the view select list contains these substitute functions. "

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLDenis]

use this

SELECT OBJECTPROPERTY(OBJECT_ID('ViewName'),'IsIndexable')

to see if you met the requirements
1 will bereturned if you did and 0 otherwise
change ViewName with the name of your view

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/