Indexed views trade extra write work and storage for potentially faster repeated reads.
Complete 12 of 24 practices (50%) and enter your name to unlock the Certificate of Participation.
A normal view stores only query definition. An indexed view becomes a physically maintained result set once a unique clustered index is created on the view.
If a dashboard repeatedly groups millions of fact rows by the same dimensions, an indexed view may store that aggregate result. But every insert, update or delete affecting those base rows must also maintain the indexed structure.
Base tables
|
+-- INSERT / UPDATE / DELETE
| |
| +--> maintain indexed view
|
+-- Query indexed view
|
+--> precomputed rows / aggregatesAn indexed view is a workload tradeoff, not a free cache.
SQL Server requires indexed views to use WITH SCHEMABINDING. The view definition becomes an explicit dependency on the underlying schema.
If a view is physically indexed, SQL Server cannot allow someone to silently drop or alter a referenced column in a way that invalidates the stored result.
CREATE VIEW dbo.vSalesSummary
WITH SCHEMABINDING
AS
SELECT
s.CustomerID,
COUNT_BIG(*) AS RowCount,
SUM(s.Amount) AS TotalAmount
FROM dbo.Sales AS s
GROUP BY s.CustomerID;SCHEMABINDING is the foundation that makes physical indexing of the view safe.
The unique clustered index is the step that transforms the schema-bound view from a logical definition into a physically maintained indexed result.
For a grouped sales view, the grouped key — such as CustomerID — may form the unique clustered index key if each grouped row is unique.
CREATE UNIQUE CLUSTERED INDEX CIX_vSalesSummary
ON dbo.vSalesSummary(CustomerID);
-- Optional later:
CREATE INDEX IX_vSalesSummary_TotalAmount
ON dbo.vSalesSummary(TotalAmount);The unique clustered index is not just an optimization; it is what materializes the indexed view.
SQL Server restricts what can appear in an indexed view because the engine must maintain the stored result incrementally and deterministically.
Many constructs that are perfectly valid in normal views are not eligible for indexed views. Aggregated designs also require patterns such as COUNT_BIG for maintainable row counts.
CREATE VIEW dbo.vSalesByCustomer
WITH SCHEMABINDING
AS
SELECT
CustomerID,
COUNT_BIG(*) AS RowCount,
SUM(Amount) AS TotalAmount
FROM dbo.Sales
GROUP BY CustomerID;The restrictions exist because SQL Server must maintain the stored result correctly on every qualifying base-table change.
Indexed views depend on required SET options. These settings are part of the feature's correctness contract, not a cosmetic preference.
Deployment scripts, ETL sessions and application connections should be validated so the required settings are consistent when maintaining or using the indexed view.
-- Example deployment checklist concept:
-- Verify required SQL Server SET options
-- before CREATE VIEW / CREATE INDEX
-- and in application connection behavior.
-- Treat settings as part of the indexed-view contract,
-- not as an afterthought.An indexed view is both a schema feature and an operational configuration feature.
Indexed views stay transactionally consistent because SQL Server maintains them when base data changes. That consistency is powerful — and it is exactly why write cost rises.
An indexed aggregate might reduce a report from seconds to milliseconds while adding measurable overhead to every insert into the fact table. Whether that trade is good depends on workload priorities.
-- Measure representative workload:
-- 1) Baseline report query
-- 2) Baseline INSERT/UPDATE batch
-- 3) Create indexed view
-- 4) Re-run report query
-- 5) Re-run write batch
-- 6) Compare:
-- logical reads
-- elapsed time
-- CPU
-- log generation
-- index/storage sizeIndexed views are justified only when total workload benefit exceeds total maintenance cost.
Indexed views are one form of precomputation, not the only one. A summary table refreshed every minute, hour or night may offer more flexibility when real-time maintenance is unnecessary.
If the dashboard can tolerate data five minutes old, a controlled ETL summary may avoid adding synchronous cost to every transaction.
Indexed View
- Current with base transaction
- Strict definition rules
- Write cost on every change
Summary Table / ETL
- Refresh on your schedule
- More transformation freedom
- Can be stale between refreshesPrecomputation is an architecture decision: indexed view when synchronous freshness matters, summary tables when controlled refresh is acceptable.
The best indexed-view candidates are stable, repeated and expensive read patterns where freshness must remain transactionally current and write overhead is acceptable.
A complex query that runs once a day may not deserve synchronous maintenance all day long. A smaller aggregate hit thousands of times per hour might.
1. Is the read pattern repeated and expensive?
2. Must results stay transactionally current?
3. Is the view definition eligible for indexing?
4. Are required SET options controlled?
5. Is SCHEMABINDING acceptable operationally?
6. Can a unique clustered key identify each view row?
7. What read improvement is expected?
8. What write overhead is introduced?
9. What storage/log impact appears?
10. Would an ETL summary table be simpler?
11. Are support teams aware of schema dependencies?
12. Does measured benefit still justify the feature after deployment?An indexed view is successful when it improves the total system workload, not merely one SELECT statement.
Open each item after answering it in your own words. The 24 interactive practices above drive certificate progress.
Creating the required unique clustered index on a schema-bound view.
It protects the indexed result from incompatible underlying schema changes.
Writes must maintain the indexed-view structure synchronously.
It gives you more control over refresh timing and transformation logic.
Measured total-workload benefit proves the design.
| Need | Strong candidate |
|---|---|
| Transactionally current repeated aggregate | Indexed View |
| Complex transformation with flexible refresh | Summary Table / ETL |
| One-time or rare expensive query | Tune query/indexes first |
| Write-heavy OLTP with little repeated read benefit | Usually avoid indexed view |
| Read-heavy stable repeated pattern | Evaluate indexed view with measurement |
Complete at least 12 of the 24 practice cases (50%) and enter your name.
Indexed views are a precomputation strategy, not a default optimization.