Views & Indexed Views

A View is a virtual table whose contents are defined by a query. They are used to simplify complex joins for report writers and to provide a layer of security. However, standard views can be slow. Indexed Views (Materialized Views) solve this by physically storing the result on the disk.

1. Standard Views (The Mask)

A standard view doesn't store data. When you query the view, SQL Server just "expands" the view's SQL into your main query. It is great for Abstraction. If you change a table name, you just update the view, and your reports don't break.

2. Indexed (Materialized) Views

When you create a unique clustered index on a view, the result of the query is calculated and Physically Persisted to the disk. Now, when you query the view, it is just as fast as querying a table! This is perfect for complex aggregations (e.g., Year-to-Date sales) that are calculated over millions of rows.

3. The SCHEMABINDING Requirement

To create an indexed view, you must use WITH SCHEMABINDING. This locks the underlying tables so you can't change their structure in a way that would break the view's physical storage.

4. Interview Mastery

Q: "Why can't my Indexed View use an OUTER JOIN or a Subquery?"

Architect Answer: "Indexed Views must be deterministic and simple so that SQL Server can automatically update the physical storage whenever the underlying tables change. `LEFT JOINs` or Subqueries introduce complexity that makes it impossible for the engine to maintain the view efficiently in real-time. If you need complex logic, you should use a standard View or a separate reporting table managed by a job."