Limitation on Views

Views.

The ANSI_NULLS and QUOTED_IDENTIFIER options must be turned on when the view is created. Following SP will turn those options on.

sp_dboption 'ANSI_NULLS', TRUE
sp_dboption 'QUOTED_IDENTIFIER', TRUE

The ANSI_NULLS option must have been turned on during the creation of all the tables that are referenced by the view.

All the tables referenced by the view must be in the same database as the view.

All the tables referenced by the view must have the same owner as the view.

Indexed view must be created with the SCHEMABINDING option. This option prohibits the schema of the base tables from being changed (adding or dropping a column, for instance). To change the tables, Indexed view must be dropped.

Any user-defined functions referenced in the view must have been created with the SCHEMABINDING option as well.

Any functions referenced in an indexed view must be deterministic; deterministic functions return the same value each time they’re invoked with the same arguments.

A column can not be referenced twice in the SELECT statement unless all references, or all but one reference, to the column are made in a complex expression.
Illegal:
SELECT qty, orderid, qty
Legal:
SELECT qty, orderid, SUM(qty)

You can’t use ROWSET, UNION, TOP, ORDER BY, DISTINCT, COUNT(*), COMPUTE, or COMPUTE BY.

The AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP aggregate functions aren’t allowed in the SELECT statement.

A SUM() that references a nullable expression isn’t allowed.

CONTAINS and FREETEXT aren’t allowed in the SELECT statement.

If you use GROUP BY, you can’t use HAVING, ROLLUP, or CUBE, and you must use COUNT_BIG() in the select list.

You can’t modify more than one table at a time through a view.

If your view is based on aggregate functions, you can’t use it to modify data.

If your view is based on a table that contains fields that don’t allow null values yet your view doesn’t display those fields, then you won’t be able to insert new data.