IndexView

A clustered Index is a type of view in which we can create an Index.
We can create one clustured and many non-clustured index on view,but the first index created on a view must be a unique


We can not create a index on all the views.

A view must meet the following 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. 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. The IsDeterministic property of the OBJECTPROPERTY function reports whether a user-defined function is deterministic or not.

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.

CREATE VIEW test
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice) AS Revenue,
OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
GO