What is an indexed view (materialized view) in SQL Server?
Quick Answer
A normal view is just a saved query — it stores no data. An indexed view has a unique clustered index, so its result set is physically stored and kept in sync automatically as the base tables change. It can massively speed up expensive aggregations or joins, at the cost of slowing every write to the underlying tables.
A regular view is a stored query, nothing more — every time you select from it, it runs the underlying query. An indexed view is materialised: you put a unique clustered index on it and SQL Server physically stores the results and maintains them on every insert, update and delete to the base tables. So a report that aggregates millions of rows becomes an index seek. The trade-off is that all those base-table writes now also have to update the view, and there's a strict list of what the view definition can contain. I use them for reporting rollups over data that doesn't change often.
- Requires
WITH SCHEMABINDINGand a long list of restrictions (noOUTER JOIN, no subqueries, deterministic only,COUNT_BIG(*)if grouped). - Enterprise edition can use an indexed view automatically; other editions need the
NOEXPANDhint. - Best for read-heavy reporting aggregates over slowly-changing data; bad for write-heavy tables.