Microsoft SQL Server CREATE VIEW CREATE Indexed VIEW
Example
To create a view with an index, the view must be created using the WITH SCHEMABINDING keywords:
CREATE VIEW view_EmployeeInfo
WITH SCHEMABINDING
AS
SELECT EmployeeID,
FirstName,
LastName,
HireDate
FROM [dbo].Employee
GO
Any clustered or non-clustered indexes can be now be created:
CREATE UNIQUE CLUSTERED INDEX IX_view_EmployeeInfo
ON view_EmployeeInfo
(
EmployeeID ASC
)
There Are some limitations to indexed Views:
-
The view definition can reference one or more tables in the same database.
-
Once the unique clustered index is created, additional nonclustered indexes can be created against the view.
-
You can update the data in the underlying tables – including inserts, updates, deletes, and even truncates.
-
You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.
-
It can’t contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements.
For more information about creating indexed Views you can read this MSDN article
