Welcome To TechBrothersIT

Views & Materialized Views in Azure Synapse Dedicated SQL Pool | Explained

📘 Introduction

Azure Synapse Analytics supports both Views and Materialized Views (also known as Indexed Views) in the Dedicated SQL Pool. While both help simplify complex queries, their behavior and performance implications differ significantly.

🔹 What is a View?

A View is a saved SQL SELECT query. It does not store data physically — every time you query the view, Synapse re-executes the underlying SQL logic. Views are useful for code reuse, abstraction, and simplifying joins or filters.

Example: Create a Simple View

CREATE VIEW dbo.vwTop100Sales1 AS
SELECT TOP (100)
    SaleID,
    ProductID,
    Quantity
FROM dbo.SalesFact_CSV
ORDER BY SaleDate DESC;

This view always returns the latest 100 sales records from the SalesFact_CSV table.

🔹 What is a Materialized View?

A Materialized View in Synapse stores the result set of a query physically. It is automatically refreshed by Synapse in the background and is useful for performance optimization — especially for repeated queries involving joins or aggregations.

Example: Create a Materialized View

CREATE MATERIALIZED VIEW dbo.mv_TotalQuantityByCustomer
WITH (
    DISTRIBUTION = HASH(CustomerID)
)
AS
SELECT
    CustomerID,
    COUNT_BIG(*) AS TotalOrders,
    SUM(ISNULL(Quantity, 0)) AS TotalQuantity
FROM dbo.SalesFact_CSV
GROUP BY CustomerID;

This view pre-aggregates the total quantity and order count by CustomerID and speeds up analytic queries significantly.

📌 Key Differences

Views are logical (no data storage).
Materialized Views are physical and improve performance by caching results.
Materialized Views are auto-refreshed in Synapse (no manual refresh needed).
Use Views for modularity and reuse; use Materialized Views for speed.

🧠 Best Practices

Use Views to encapsulate query logic for reuse across reports.
Use Materialized Views to accelerate queries on large datasets or frequent joins.
Choose appropriate DISTRIBUTION strategy (e.g., HASH for large aggregations).
Regularly monitor performance to determine when a view should be upgraded to a materialized view.

🔄 Demo Recap from SQL File

vwTop100Sales1 – Standard View showing top 100 latest sales.
mv_TotalQuantityByCustomer – Materialized View for preaggregated sales data by customer.
Materialized View will automatically reflect changes in source data (with some lag).

📺 Watch the Video Tutorial

📚 Credit: Content created with the help of ChatGPT and Gemini.