Indexed Views: When the Faster Reads Are Worth It

A report can read a precomputed sales summary instead of recomputing it from base rows. Indexed views make that possible, but every write to the source tables pays to keep the summary current.

Pick a Stable Summary for Indexed Views

Start with a report that repeats the same join or aggregate over a large stable set. An indexed view stores its result through a unique clustered index. The view definition has restrictions: SCHEMABINDING, deterministic expressions, two-part object names, and COUNT_BIG when grouping. The session SET options must meet indexed-view requirements. I design the grouping key first so the unique index has an obvious key. A summary by date and product is a different object from a summary by customer and month; do not build a view that tries to serve every report.

Check whether a simpler covering index or query rewrite solves the report first. Indexed views add maintenance to every source write, so the read benefit needs to be significant and repeated.

Build the View in a Test Database

The following shape assumes a dbo.Sales table with SaleDate, ProductID, and a NOT NULL Amount. Create the base table in a disposable database before adapting it to your schema. Keep the required SET options on during creation and DML. I include COUNT_BIG even when the report does not show it because grouped indexed views require it.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
GO
CREATE VIEW dbo.SalesSummary WITH SCHEMABINDING AS
SELECT SaleDate, ProductID, COUNT_BIG(*) AS sale_count,
       SUM(Amount) AS total_amount
FROM dbo.Sales
GROUP BY SaleDate, ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX CX_SalesSummary
ON dbo.SalesSummary(SaleDate, ProductID);

Compare Read Work

Run the original aggregate and a query against the view with the same filters and returned columns. Capture actual plans and SET STATISTICS IO. Check whether the view index is really used. On SQL Server Standard edition, use NOEXPAND to query the indexed view directly. Automatic indexed-view matching depends on edition and platform, so verify the plan rather than assuming the optimizer found your summary. I keep the predicates and parameters identical for the comparison. A different date range can make any plan look better. In the block below, OPTION (EXPAND VIEWS) keeps the first query on the base table, so automatic view matching cannot blur the comparison.

SET STATISTICS IO ON;
SELECT SaleDate, ProductID, COUNT_BIG(*) AS sale_count,
       SUM(Amount) AS total_amount
FROM dbo.Sales
WHERE SaleDate >= '20260101' AND SaleDate < '20260201'
GROUP BY SaleDate, ProductID
OPTION (EXPAND VIEWS);
SELECT SaleDate, ProductID, sale_count, total_amount
FROM dbo.SalesSummary WITH (NOEXPAND)
WHERE SaleDate >= '20260101' AND SaleDate < '20260201';
SET STATISTICS IO OFF;

Measure the Write Tax of Indexed Views

Insert, update, and delete rows in a restored copy while the indexed view exists, then repeat after removing it from an identical starting point. Measure transaction duration, log bytes, blocking, and CPU under the application's write concurrency. A summary with a hot grouping key can serialize writers. I check waits and lock duration, not only average insert time. The view also takes storage and affects maintenance operations. A read improvement is worth it only when the full workload benefits.

Do not ignore updates that move a row between groups. They change two summary groups, and their maintenance cost can differ from a simple insert.

Keep the Definition Maintainable

Schema binding prevents changes to referenced base columns until the view dependency is handled. Include that in deployment plans. Review the view after table changes, SET option changes in clients, and compatibility updates. A client session with the wrong required SET options can fail DML against an indexed-view base table. I test the application connection settings, not only my SSMS session. That operational detail has surprised more teams than the CREATE VIEW syntax.

Would the report still improve if the same aggregate were materialized by a scheduled reporting table? That alternative tolerates some staleness but removes synchronous write cost. Choose based on freshness requirements.

Check Query Matching and Session Options

An indexed view exists on disk, but a report can still read the base tables. Confirm the plan uses the view's clustered index when you test the read benefit. On Standard edition, reference the view with NOEXPAND for direct use. On other supported platforms, automatic matching still depends on optimizer eligibility and cost. I check the query's SET options, predicates, and grouping against the view definition. If the report uses a different expression or filter, the stored summary can be irrelevant.

Client SET options also affect writes to base tables that have indexed views. Test inserts and updates through the actual application driver. A DBA session with the required options can pass while an older application connection fails. I keep that compatibility test in the release checklist, not as an afterthought.

Measure Contention on Hot Groups

The view index maintains aggregate rows when source rows change. If many transactions update one group, they contend on that summary row even when base rows differ. I use a write test with realistic concurrent sessions, not a single INSERT loop. Record blocking, deadlocks, log growth, and transaction duration. A report that becomes much faster can still leave the checkout path slower. The right answer depends on the balance of those workloads.

What if the report tolerates a few minutes of delay? A scheduled summary table can move maintenance out of the write transaction, at the price of staleness and refresh logic. I compare that option with the indexed view before committing to synchronous maintenance. The decision should state freshness, read latency, write cost, storage, and operational owner. An indexed view is valuable when it earns its maintenance cost in the workload that actually runs.

Include delete and update tests in the write comparison, not only inserts. A changed sale amount updates a summary group, and a changed grouping key moves work between groups. I check the most common application write paths and their transaction duration. That is where the maintenance tax appears for real users, even if a single insert benchmark looks harmless.

Decide on Indexed Views From Both Sides

Keep before and after evidence for report reads and base-table writes. Include the view's storage and maintenance obligations. If reads dominate and current data matters, the indexed view can earn its keep. If writes are hot or reports are infrequent, a different design is likely better. I revisit the decision as workload balance changes; an indexed view created for a busy report can linger after the report is retired.

The plan should show the indexed view when you expect it, and the write test should show its price. Without both, the decision rests on a demo that only measured the attractive half.

Related reading on this blog: Using NOEXPAND with Indexed View and Applying Query Hints to Views.

An indexed view is not free speed, it is read work moved to every write.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.