1. Introduction
Many enterprise systems need fast, real-time analytics on transactional data. Examples include:
When queries involve large tables, multiple joins, or expensive aggregates, API response time becomes slow. Index tuning sometimes helps, but often it is not enough.
This is where materialized views (also known as indexed views) in SQL Server provide a huge advantage. They precompute and store aggregated results physically, giving near-instant reads.
This article explains how to use SQL Server indexed views for complex aggregates using a practical, step-by-step approach.
2. What is a Materialized View?
A materialized view in SQL Server is simply an indexed view. It stores the result of the view physically on disk, unlike a normal view which is just a SELECT query.
Key properties
Data is precomputed and stored.
Automatically updated when base tables change.
Querying the view is extremely fast.
SQL Server may rewrite queries automatically to use the indexed view.
Materialized views are perfect for slow aggregate queries in OLTP workloads.
3. Example Scenario
Assume you have an Orders table with millions of rows:
CREATE TABLE dbo.Orders (
OrderId BIGINT PRIMARY KEY,
CustomerId INT,
OrderDate DATETIME2,
Amount DECIMAL(18,2),
Status VARCHAR(20)
);
Your application frequently runs this expensive query:
SELECT
CustomerId,
COUNT(*) AS TotalOrders,
SUM(Amount) AS TotalAmount
FROM dbo.Orders
WHERE Status = 'Completed'GROUP BY CustomerId;
This scan can take several seconds when the table becomes large.
A materialized view makes this instant.
4. Requirements for Indexed Views
SQL Server enforces strict rules for creating indexed views:
4.1 Mandatory Requirements
Schema binding
Deterministic expressions
Two-part naming for tables
NO COUNT(*), use COUNT_BIG
NO TOP, DISTINCT, UNION, OUTER JOIN
Grouping allowed but only on explicit columns
4.2 Recommended Settings
Execute these at database level:
ALTER DATABASE YourDB SET ANSI_NULLS ON;
ALTER DATABASE YourDB SET QUOTED_IDENTIFIER ON;
ALTER DATABASE YourDB SET ANSI_PADDING ON;
ALTER DATABASE YourDB SET ANSI_WARNINGS ON;
ALTER DATABASE YourDB SET CONCAT_NULL_YIELDS_NULL ON;
Without this, SQL Server will not allow an indexed view.
5. Creating a Materialized View (Step-by-Step)
Step 1: Create the View
CREATE VIEW dbo.vwCustomerOrderSummary
WITH SCHEMABINDING
ASSELECT
o.CustomerId,
COUNT_BIG(*) AS TotalOrders,
SUM(o.Amount) AS TotalAmount
FROM dbo.Orders AS o
WHERE o.Status = 'Completed'GROUP BY o.CustomerId;
Note the use of:
Step 2: Create a Unique Clustered Index
CREATE UNIQUE CLUSTERED INDEX IX_vwCustomerOrderSummary
ON dbo.vwCustomerOrderSummary (CustomerId);
This makes the view materialized.
Now SQL Server stores aggregated results physically.
6. How SQL Server Maintains the View
Whenever a row is inserted, updated, or deleted in the Orders table:
SQL Server recalculates affected aggregates.
The indexed view is updated transactionally.
Reads become extremely fast since the work is precomputed.
Trade-off
This makes indexed views ideal for read-heavy systems.
7. Querying the Materialized View
Your API can query the view directly:
SELECT *
FROM dbo.vwCustomerOrderSummary
WHERE CustomerId = 10;
This returns results instantly because the aggregates are already computed.
Automatic Query Rewrite
Even if you do not query the view directly, SQL Server may rewrite queries internally to use the indexed view—if:
Example
SELECT
CustomerId,
COUNT(*) AS TotalOrders
FROM dbo.Orders
WHERE Status = 'Completed'GROUP BY CustomerId
OPTION (NOEXPAND);
This forces SQL Server to use the materialized view.
8. Materialized Views for Complex Aggregates
Let’s explore advanced use cases.
8.1 Daily Summary (Partition-Aware Aggregates)
CREATE VIEW dbo.vwDailySalesSummary
WITH SCHEMABINDING
ASSELECT
CAST(OrderDate AS DATE) AS OrderDay,
COUNT_BIG(*) AS TotalOrders,
SUM(Amount) AS TotalAmount
FROM dbo.Orders
GROUP BY CAST(OrderDate AS DATE);
Index
CREATE UNIQUE CLUSTERED INDEX IX_vwDailySalesSummary
ON dbo.vwDailySalesSummary(OrderDay);
Ideal for dashboards showing:
Today’s sales
Yesterday’s sales
Trend charts
8.2 Multi-Level Aggregates
Materialized views support multiple grouping levels inside a single view.
Example: Customer + Month aggregates:
CREATE VIEW dbo.vwCustomerMonthlySummary
WITH SCHEMABINDING
ASSELECT
CustomerId,
YEAR(OrderDate) AS Yr,
MONTH(OrderDate) AS Mn,
COUNT_BIG(*) AS TotalOrders,
SUM(Amount) AS TotalAmount
FROM dbo.Orders
GROUP BY CustomerId, YEAR(OrderDate), MONTH(OrderDate);
Index:
CREATE UNIQUE CLUSTERED INDEX IX_vwCustomerMonthlySummary
ON dbo.vwCustomerMonthlySummary (CustomerId, Yr, Mn);
8.3 Rolling Aggregates Using Partitioned Base Tables
If the Orders table is partitioned (e.g., by date), the indexed view must also be partition-aligned.
Benefits
9. Performance Benefits
9.1 Query Speed
Typical gains:
Aggregate reports: 20x to 100x faster
Dashboard queries: sub-millisecond
Complex joins: significant reduction in CPU
Example before vs after
| Operation | Before | After (Indexed View) |
|---|
| Aggregation of 10M rows | 5 seconds | 20 ms |
| Dashboard load | 2 seconds | 5 ms |
| Top customers report | 1 second | 10 ms |
10. Common Mistakes and How to Avoid Them
10.1 Using non-deterministic functions
Functions like:
cannot be used.
Replace GETDATE() with computed date columns stored in the table.
10.2 Including columns not part of GROUP BY
Every non-aggregated column must be in GROUP BY.
10.3 Forgetting SCHEMABINDING
Without this, SQL Server rejects the view.
10.4 Using COUNT instead of COUNT_BIG
COUNT_BIG is mandatory.
10.5 Expecting automatic rewrite in Standard Edition
In Standard Edition, queries use the indexed view only when WITH (NOEXPAND) is specified.
11. Use Cases Where Materialized Views Shine
11.1 Analytical dashboards
Precompute revenue, sales, customer metrics.
11.2 Inventory aggregation
QuantityOnHand
QuantityAvailable
SafetyStock
11.3 Financial metrics
Daily transaction sums
Outstanding balances
11.4 Aggregation-heavy reporting
Eliminate workload on transactional tables.
11.5 Reducing CPU-heavy queries
Materialized views significantly reduce CPU consumption.
12. When Not to Use Materialized Views
12.1 Write-heavy tables
Materialized views slow down inserts/updates.
12.2 Very high cardinality dimensions
Large GROUP BY keys may produce huge materialized views.
12.3 Frequently dropping/re-creating base tables
SCHEMABINDING prevents schema changes.
13. Materialized Views vs Other Optimization Techniques
| Technique | Purpose |
|---|
| Indexed view | Fast aggregated queries |
| Table partitioning | Manage large data volumes |
| Cache layer (Redis) | Offload repeated reads |
| Pre-computed tables | Manual update, flexible |
| OLAP cubes | Multi-dimensional analytics |
Indexed views are perfect for real-time aggregates inside the OLTP database itself.
14. Maintenance Strategy
14.1 Rebuild indexes periodically
ALTER INDEX ALL ON dbo.vwCustomerOrderSummary REBUILD;
14.2 Monitor fragmentation
Materialized views can fragment quickly.
14.3 Use partition switching for bulk loads
Minimize view updates on large inserts.
Conclusion
SQL Server materialized views (indexed views) are one of the most powerful ways to speed up complex aggregate queries. They provide:
They are ideal for systems with heavy read workloads where aggregates are common.
Use them when:
Queries hit large tables repeatedly
Aggregates slow down the system
You want real-time reporting without moving to a data warehouse
Avoid them when:
When implemented correctly, indexed views provide enterprise-grade performance improvements with very little operational overhead.