Materialized Views

Definition of Materialized Views: Materialized views are also the logical virtual copy of data-driven by the select query but the result of the query will get stored in the table or disk.
Definition of Views: Technically View of a table is a logical virtual copy of the table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables.

Benefits of using Materialized Views.
1. Redue the execution time for complex queries, Higher the potential for execution time saving.
2. Optimizer in SQL Pool can automatically used deployed materialized views to improve query execution plans.
3. Require low maintenance of the views. All incremental data changes from the base tables are automatically added to the materialized views in a synchronous manner.
4. The data in a materialized view can be distributed differently from the base tables.

Common scenarios

1. Need to improve the performance of complex analytical queries against large data in size
2. Need faster performance with no or minimum query changes.
3. Need different data distribution strategy for faster query performance

Syntax

CREATE MATERIALIZED VIEW [ schema_name. ] materialized_view_name
    WITH (  
      <distribution_option>
    )
    AS <select_statement>
[;]

<distribution_option> ::=
    {  
        DISTRIBUTION = HASH ( distribution_column_name )  
      | DISTRIBUTION = ROUND_ROBIN  
    }

<select_statement> ::=
    SELECT select_criteria

Arguments

schema_name
Is the name of the schema to which the view belongs.
materialized_view_name
Is the name of the view. View names must follow the rules for identifiers. Specifying the view owner name is optional.
distribution option
Only HASH and ROUND_ROBIN distributions are supported.
select_statement
The SELECT list in the materialized view definition needs to meet at least one of these two criteria:The SELECT list contains an aggregate function.
GROUP BY is used in the Materialized view definition and all columns in GROUP BY are included in the SELECT list. Up to 32 columns can be used in the GROUP BY clause.
Aggregate functions are required in the SELECT list of the materialized view definition. Supported aggregations include MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

The materialized view will be disabled when an UPDATE or DELETE occurs in the referenced base tables.  This restriction doesn’t apply to INSERTs.  To re-enable the materialized view, run ALTER MATERIALIZED VIEW with REBUILD.

Reference Link
Link from Microsoft
Link from Microsoft