SQL Server Indexed View (Materialized View)

View does not occupy space unless it is a materialized view. When index is created on a view then that view is known as materialized view. It is created to improve query performance. The index on view can significantly improve query performance, especially for aggregations, joins, and complex queries. The index is automatically maintained by the database engine whenever the underlying data changes. Indexed view should not be created if the table from which view is created is highly transactional (i.e. a lot of action queries are associated with the table). In this case, synchronization between view and table will be very time consuming affair.

We need to use schema binding on the view to create index on the view. Schema binding implies that table cannot be dropped as it has binding with view. Look at the following example.

CREATE VIEW MyIndexedView
WITH SCHEMABINDING
AS
SELECT Column1, SUM(Column2) AS Total
FROM MyTable
GROUP BY Column1;

CREATE UNIQUE CLUSTERED INDEX IX_MyIndexedView ON MyIndexedView(Column1);

We can create additional non-clustered indexes on the view

Key Characteristics of Materialized View:

• The data of materialized view is physically stored in database.
• Indexed views can have a clustered index, improving query performance by providing a structured storage format.
• The index is automatically maintained by the database engine whenever the underlying data changes.