All 7 Views questions
More in Views
What is a view in SQL Server, and what benefits does it provide? Can you insert, update, or delete data through a view, and what conditions must be met for this to work? What is a schema bound view, and what specific benefit does the WITH SCHEMABINDING option provide? How do you use a view to help enforce a basic level of security by limiting which columns or rows different groups of users can see? How would you design a layered set of views to progressively simplify a complex data model for different types of users, such as analysts versus casual business users? What happens to the performance and correctness of a view if the underlying tables change significantly after the view was originally created?
Advertisement
SQL Server › Views

What is an indexed view, and how can it improve the performance of complex, frequently run queries?

Intermediate

Answer

An indexed view, also called a materialized view, physically stores the results of a view's query on disk with its own unique clustered index, rather than recalculating the query every time it is accessed, which can dramatically speed up complex aggregations or joins that are queried frequently, at the cost of additional storage and some overhead when the underlying data changes.
CREATE VIEW dbo.SalesSummary
WITH SCHEMABINDING
AS
SELECT ProductId, SUM(Amount) AS TotalSales, COUNT_BIG(*) AS SaleCount
FROM dbo.Sales
GROUP BY ProductId;

CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary ON dbo.SalesSummary(ProductId);
Real-world example A reporting dashboard uses an indexed view to pre-calculate sales totals by product, delivering near instant results for a calculation that would otherwise require scanning and aggregating millions of rows on every single request.

Common follow-ups: What additional overhead does an indexed view add to write operations on the underlying tables?;What edition of SQL Server is required to fully benefit from indexed views?

Columnstore Indexes;Query Optimization & Plans
All 7 Views questions