SQL Indexed Views

What Are Indexed Views?

An indexed view is a database object that stores the result of a view query physically on disk by creating a unique clustered index on it.

Unlike regular views, which are virtual and compute results on demand, indexed views improve performance by avoiding repeated calculations.

They are especially useful for complex queries involving joins and aggregations.

• Materializes the view's result set.
• Requires a unique clustered index.
• Improves query speed by precomputing data.
• Supported in SQL Server and some other RDBMS.

Benefits of Using Indexed Views

Indexed views can significantly improve query performance by storing precomputed results.

They reduce CPU and IO overhead for frequently executed complex queries.

• Faster query execution for complex joins and aggregations.
• Reduced CPU usage by avoiding repeated computations.
• Improved response times for reporting and analytics.
• Automatic maintenance of the indexed view when base tables change.

Creating an Indexed View in SQL Server

To create an indexed view, first define a standard view with the desired query.

Then create a unique clustered index on the view to materialize it.

Certain restrictions apply to the view definition to allow indexing.

• The view must be created with SCHEMABINDING.
• The SELECT statement must follow specific rules (e.g., no outer joins, no TOP, no UNION).
• All referenced objects must be in the same database and schema.
• The view must have a unique clustered index.

Example: Creating an Indexed View

Suppose you have a Sales table and want to create an indexed view that sums sales by product.