Can you have an index on a view? If yes, how?

Question

Accepted Answer

Yes, some databases support indexing a view, though it requires the view to meet specific criteria and is called a materialized view or an indexed view depending on the system. Once indexed, the view's result set is physically stored and automatically kept in sync, rather than being recomputed on every query.

Key Points:
• In SQL Server, you create an indexed view by defining the view WITH SCHEMABINDING and then creating a unique clustered index on it, which materializes the data.
• In Oracle and PostgreSQL, the equivalent concept is a materialized view, created with CREATE MATERIALIZED VIEW, which can itself have indexes on top of it.
• Indexed/materialized views trade write overhead and storage for much faster reads on complex aggregations or joins.
• Not every view qualifies -- restrictions typically apply around non-deterministic functions, outer joins, and certain aggregate combinations depending on the database.
• MySQL does not support true indexed views; equivalent behavior there is usually achieved with a manually maintained summary table.

Example:
A reporting dashboard that repeatedly aggregates millions of order rows into daily totals can create an indexed/materialized view of that aggregation once, so subsequent dashboard queries read from the precomputed, indexed result instead of recalculating it every time.

Code Example:
```
-- SQL Server indexed view
CREATE VIEW dbo.OrderTotals WITH SCHEMABINDING AS
SELECT customer_id, SUM(amount) AS total_amount, COUNT_BIG(*) AS order_count
FROM dbo.orders
GROUP BY customer_id;

CREATE UNIQUE CLUSTERED INDEX IX_OrderTotals
ON dbo.OrderTotals(customer_id);

-- Oracle / PostgreSQL materialized view
CREATE MATERIALIZED VIEW order_totals AS
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;
```

Interview Tip:
A concise interview answer is:

"Yes -- in SQL Server you build an indexed view by declaring it WITH SCHEMABINDING and adding a unique clustered index, which physically materializes the result, and in Oracle or PostgreSQL you'd use a materialized view instead. I reach for this when a view aggregates a lot of data and is queried repeatedly, since it trades some write overhead and refresh complexity for much faster reads. MySQL doesn't support true indexed views, so there I'd fall back to a manually maintained summary table."