Answer
Indexed views materialize the result of a view and maintain it on disk like a physical table. They are backed by a clustered index and optionally additional nonclustered indexes.
When to use:
- Heavy, repetitive aggregations or joins over large datasets (e.g., reporting queries over transactional tables).
- Scenarios where read performance is critical and data changes are relatively moderate.
- To precompute expensive expressions and reduce CPU usage under analytic workloads.
Trade-offs:
- Every insert/update/delete on the underlying tables must update the indexed view, increasing DML overhead.
- Strict requirements apply (e.g., schema binding, deterministic expressions).
- Can complicate troubleshooting if developers are unaware of their presence.
They are powerful for read-intensive workloads but should be used selectively and measured carefully in write-heavy systems.