Indexed Views in SQL Server

What Are Indexed Views?

A regular view in SQL Server is a virtual table that does not store data physically; it runs the underlying query each time it is accessed.

An indexed view, however, stores the result set physically by creating a unique clustered index on the view.

This materialization allows SQL Server to retrieve data faster, especially for complex queries involving aggregations or joins.

• Indexed views improve performance by avoiding repeated computation.
• They behave like materialized views in other database systems.
• Only one clustered index can be created on a view.

Requirements and Restrictions for Indexed Views

SQL Server imposes several requirements and restrictions to create an indexed view to ensure data consistency and performance.

The view must be created with the SCHEMABINDING option to prevent changes to underlying tables that would invalidate the view.

Certain SQL constructs are not allowed in indexed views.

• View must be created with SCHEMABINDING.
• All referenced objects must be in the same database and schema.
• No use of OUTER JOIN, UNION, TOP, or subqueries.
• Deterministic functions only; no non-deterministic functions allowed.
• Aggregations allowed only with COUNT_BIG, SUM, MIN, MAX, and COUNT.
• No text, ntext, image, or varchar(max) columns.
• The view must have a unique clustered index.

Creating an Indexed View: Example

Let's create a simple indexed view that aggregates sales data by product.

First, ensure the base tables exist and the view meets all requirements.

Example SQL Code

Below is the SQL code to create the indexed view.