What is a view, and what is an indexed view?
Quick Answer
A view is a saved SELECT query you can treat like a table. It stores no data — it runs its query each time you use it. An indexed view (SQL Server's materialized view) adds a unique clustered index so the results are physically stored and kept up to date, speeding up expensive aggregations at the cost of slower writes to the base tables.
A view is basically a named query that behaves like a table but stores nothing — it re-runs its SELECT every time. I use views to hide a complicated join, or to expose only certain columns for a reporting user. An indexed view is SQL Server's version of a materialized view: you put a unique clustered index on it, and now the results are actually stored on disk and maintained automatically, which is great for pre-aggregating something expensive, but it slows down every write to the underlying tables.
- Uses of a view: hide join complexity, expose a subset of columns for security, provide a stable interface over a changing schema.
- Updatable views: possible if the view maps unambiguously to one base table (no aggregates, no
DISTINCT, etc.); otherwise use anINSTEAD OFtrigger. - Indexed views need
WITH SCHEMABINDING, deterministic expressions, and have restrictions (no outer joins, no subqueries). Every base-table write updates the index. - A view does not by itself improve performance — the optimiser inlines it into the outer query.