Microsoft SQL Server 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