Answer.

An Indexed View (materialized view with an index) is a view for which a unique clustered index has been created. This allows the results of the query to be physically stored on disk and the index to be used to speed up access.

Advantages:

• Fast retrieval of aggregated or frequently used data.
• Reduces the load of real-time computations.

Limitations:

• Not all types of queries are supported (for example, TEXT, NTEXT, IMAGE data types cannot be used, some functions).
• Additional costs to maintain the index when changes are made to the base table.
• Requires adherence to a strict set of rules when declared.

Code Example

CREATE VIEW dbo.SalesSummary WITH SCHEMABINDING AS
   SELECT SalesPersonID, COUNT_BIG(*) AS SalesCount, SUM(TotalDue) AS TotalDue
   FROM dbo.Sales
   GROUP BY SalesPersonID;
GO
CREATE UNIQUE CLUSTERED INDEX idx_SalesSummary 
   ON dbo.SalesSummary (SalesPersonID);

Trick Question.

Q: Do all views allow an index (clustered) to be created on them?

A: No, only those views that fully meet the restrictions can be indexed (for example, no external references, agreed aggregate functions, etc.). Otherwise, the index creation query will return an error.

Examples of real errors due to ignorance of the nuances of the topic.

---

Story

In a real banking system project, it was required to speed up queries on aggregated reports. An indexed VIEW was created without analyzing the load on updating tables. The performance of the main table dropped sharply during mass insert operations due to significant costs for updating the view index.

---

Story

A specialist mistakenly thought that a VIEW with a specified JOIN on an external table and the GETDATE() function could be indexed. The index creation query failed with the error "Cannot create index on view with non-deterministic functions". A day was spent fixing the project architecture.

---

Story

In a large e-commerce project, updating data in the base table "Products" caused lock issues. The reason was improper transaction management in the presence of a large indexed VIEW, whose aggregation affected millions of rows.