Materialized View in SQL Server (Indexed View)

Concept and Terminology

SQL Server does not use the term Materialized View in its syntax or documentation.
Instead, SQL Server implements the same concept under the name Indexed View.

An Indexed View is a database object where:

• The view’s result set is physically stored on disk

• Data is materialized and persisted

• The SQL Server engine automatically maintains the stored data as base tables change

Functionally, an Indexed View is a materialized view, even though the naming differs.

Core Characteristics

Physical Storage

• The view’s data is stored as a clustered index

• Once the clustered index exists, the view becomes materialized

• Additional nonclustered indexes may be created on top of it

Automatic Maintenance

• SQL Server keeps the indexed view transactionally consistent

• Any INSERT, UPDATE, or DELETE on base tables automatically updates the view

• Maintenance occurs synchronously as part of the same transaction

Deterministic Definition

To be eligible for materialization, the view definition must be:

• Deterministic

• Schema-bound

• Based only on supported constructs
  This ensures predictable and repeatable results.

Query Optimization and Usage

Optimizer Integration

• The SQL Server optimizer may choose to use the indexed view instead of base tables

• This can happen even if the query does not explicitly reference the view

• The decision is cost-based

Edition Differences

• In Enterprise Edition, the optimizer can automatically use indexed views

• In Standard and lower editions, explicit hints may be required for usage

Performance Purpose

Indexed views are designed to:

• Precompute expensive aggregations

• Reduce CPU cost for complex analytical queries

• Improve performance for repetitive, read-heavy workloads

• Shift computation cost from query time to data modification time

They are especially effective in:

• Reporting systems

• Data warehouse–like workloads

• Aggregation-heavy dashboards

Transactional Impact

Write Performance Cost

• Every modification to base tables incurs additional overhead

• Changes must also update the indexed view

• High-frequency OLTP workloads may suffer performance degradation

Locking and Concurrency

• Indexed view maintenance participates in the same transaction

• Lock escalation risk increases under heavy write concurrency

• Poorly designed indexed views can become contention points