What is an indexed view in SQL Server?

An indexed view (also called a materialized view) in SQL Server is a view that has a

unique clustered index created on it. This results in the view storing the query results

physically in the database, similar to a table. It is useful for improving performance when you

have complex queries that aggregate data, as the results of the view are precomputed and

stored.

• Benefits:
• Faster read performance for complex aggregations or queries.
• Reduces the need for recomputing the result of a complex query each time

the view is accessed.

• Downside:
• Insert, update, or delete operations on the underlying tables will incur

additional overhead because the indexed view must also be updated.

Example:

CREATE VIEW employee_summary AS

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department;

• - Create a clustered index on the view

CREATE UNIQUE CLUSTERED INDEX idx_employee_summary

ON employee_summary(department);