11. Views and Materialized Views

In SQL, Views and Materialized Views are essential tools for both simplifying complex queries and significantly improving reporting performance. They don't store data themselves (in the case of a standard View) but rather act as permanent, pre-defined windows into your underlying data.

---

1. Views: The Virtual Table

A View is a saved SQL query that is stored in the database as a virtual table. When you query a View, the database executes the underlying SQL query in real-time.

Purpose and Usage

• Simplification: Views hide the complexity of joining multiple tables. Instead of writing a complex 5-table JOIN query every time, you simply SELECT * FROM SalesReportView.

• Security: You can restrict a user's access to only certain columns and rows by granting permission to the View, while denying access to the sensitive base tables.

• Logic Abstraction: If the definition of a calculation (like "Active Users") changes, you update the View once, and all reports and applications relying on it are automatically updated.

Creation Example

This View combines employee names with their department names, hiding the complexity of the JOIN.

CREATE VIEW EmployeeDepartmentView AS
SELECT
    E.Name,
    D.DeptName,
    E.Salary
FROM
    Employees E
INNER JOIN
    Departments D ON E.DeptID = D.DeptID
WHERE
    E.IsActive = TRUE;

Key Feature: Real-Time Data

Views do not store data; they are just stored SQL code. Every time the View is queried, it pulls the very latest data from the base tables.

---

2. Materialized Views: The Performance Snapshot

A Materialized View (MV) is similar to a standard View, but it is different in a critical way: it stores the results of the query in a dedicated physical table.

Purpose and Usage

• Performance: MVs are designed for speed. They pre-calculate and store the results of complex aggregations (like SUM,AVG,GROUP BY) or slow, multi-table joins.

• Reporting: Ideal for dashboards and reports that query the same, large data set repeatedly, as the database simply reads from a static table rather than re-running the complex query.

Creation Example

This MV calculates total sales per city and stores the result.

CREATE MATERIALIZED VIEW CitySalesSummary AS
SELECT
    C.City,
    SUM(S.Quantity * P.Price) AS TotalRevenue
FROM
    Sales S
JOIN
    Customer C ON S.CustomerID = C.CustomerID
JOIN
    Product P ON S.ProductID = P.ProductID
GROUP BY
    C.City;

Key Feature: Refreshment

Since the data is static, MVs must be periodically updated or "refreshed."

• Manual Refresh: The MV is only updated when you explicitly run the refresh command (e.g., REFRESH MATERIALIZED VIEW CitySalesSummary;).

• Scheduled Refresh: In production environments, MVs are often refreshed automatically on a schedule (e.g., every hour, or once nightly) to balance data freshness against performance gains.