SQL Server Indexed VIEW

 To create a view with an index, the view must be created using the WITH SCHEMABINDING keywords.

Creating indexed views differs from creating normal views in that using the SCHEMABINDING hint is not optional. This means that you will not be able to apply structure changes on the tables that may affect the indexed view unless you alter or drop that indexed view first. 

In addition, you need to specify two parts name of these tables including the schema with the table name in the view definition. Also, any user-defined function that is referenced by the created indexed view should be created using WITH SCHEMABINDING hint.

Once the Indexed view is created, its data will be stored in your database the same as any other clustered index, so the storage space for the view’s clustered index should be taken into consideration.

You can benefit from indexed views if its data is not frequently updated, as the performance degradation of maintaining the data changes of the indexed view is higher than the performance enhancement of using this Indexed View. 

Indexed views improve the performance of queries that use joins and aggregations in processing huge amount of data and are executed very frequently. The environments that are best suited to indexed views are data warehouses and the Online Analytical Processing (OLAP) databases.

CREATE VIEW view_EmployeeInfo
WITH SCHEMABINDING
AS
SELECT EmployeeID,
        FirstName,
        LastName,
        HireDate
FROM [dbo].Employee

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.