Materialized views

Emulation context

In Azure Synapse Analytics (dedicated SQL pools), materialized views are used to persist the results of a query for performance optimization. They are defined using the CREATE MATERIALIZED VIEW syntax and typically include a WITH clause specifying a distribution strategy (e.g., ROUND_ROBIN, HASH(column)).

For example:

CREATE MATERIALIZED VIEW QueueStats
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT
    QueueID,
    COUNT(*) AS QueueLength
FROM ServiceQueue
GROUP BY QueueID;

Materialized views in Synapse:

• Improve query performance by storing precomputed results.

• Automatically update when underlying tables change.

• Support features like aggregation, filtering, and window functions.

Materialized views in Synapse must also follow specific restrictions:

• The query must include at least one aggregate or a GROUP BY.

• Views cannot reference other views.

• Certain aggregate functions such as COUNT(DISTINCT ...) are not supported.

• Only HASH and ROUND_ROBIN distribution strategies are allowed.

However, Microsoft Fabric Warehouse does not support materialized views. Fabric only supports logical views (CREATE VIEW) and does not persist query results or maintain physical storage for view outputs. There is no equivalent to CREATE MATERIALIZED VIEW in Fabric SQL.

---

Emulation strategy

SQL Tran does not attempt to emulate materialized views in Fabric. Instead, it performs the following:

• Converts CREATE MATERIALIZED VIEW into CREATE VIEW, preserving the original view definition.

• Comments out the MATERIALIZED keyword and any WITH (DISTRIBUTION = ...) clause using /* SQLTRAN FIX */.

• Preserves all query logic, including aggregations, joins, filtering, DISTINCT, TOP, ORDER BY, window functions, subqueries, and nested aggregates.

• Adds /* SQLTRAN FIX: Subquery */ to nested queries to flag complex constructs.

• Retains references to other views, even though these are not allowed in Synapse materialized views.

• Does not emit diagnostics or warnings about the lack of persistence or refresh behavior in Fabric.

This approach ensures that translated views remain syntactically valid and reflect the original query logic, while signaling the unsupported materialization feature through comments. The actual behavior of query result caching or automatic refresh is not replicated.

---

Code example

Synapse Analytics:

CREATE MATERIALIZED VIEW SalesSummary
WITH (
	DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT
	SalesPersonID,
	SUM(TotalDue) AS TotalSales,
	COUNT_BIG(*) AS NumberOfOrders
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID;

Fabric Warehouse (generated by SQL Tran):

CREATE /* SQLTRAN FIX: MATERIALIZED */ VIEW SalesSummary
/* SQLTRAN FIX: WITH (
	DISTRIBUTION = ROUND_ROBIN
) */
AS
SELECT
	SalesPersonID,
	SUM(TotalDue) AS TotalSales,
	COUNT_BIG(*) AS NumberOfOrders
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID;

Materialized view emulation in SQL Tran

---

Important notes

• Materialization is not preserved: Fabric views do not store query results. Translated views must be considered logical only, and no performance benefit from materialization is retained.

• Optimizer substitution not preserved: In Synapse, the optimizer may automatically substitute a materialized view during query execution. This behavior does not exist in Fabric. Queries must explicitly reference any precomputed logic if needed.

• Distribution clause is removed: The WITH (DISTRIBUTION = ...) metadata is commented out because Fabric does not support specifying distribution on views.

• Query logic fully preserved: SQL Tran retains all constructs inside the view body:

  • GROUP BY, COUNT_BIG, SUM, JOIN

  • DISTINCT

  • TOP and ORDER BY

  • Window functions (RANK(), DENSE_RANK())

  • Subqueries and nested aggregates

• Subqueries flagged for traceability: SQL Tran adds /* SQLTRAN FIX: Subquery */ to subqueries in FROM clauses to indicate nesting. These annotations are informational and may help users identify areas for optimization.

• No refresh behavior: Materialized views in Synapse are automatically refreshed when the base tables change. In Fabric, views are re-evaluated on each execution and do not persist data between queries.

• Manual optimization required: Users may need to manually replicate materialized view functionality using CTAS (Create Table As Select) patterns or scheduled refresh logic if persistent precomputed data is needed.

• Manual review recommended: Queries relying on precomputed performance should be manually evaluated to determine if alternative mechanisms (e.g., preloading, ETL staging) are needed in Fabric.