CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL) (preview)

APPLIES TO: noSQL Server noAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

This article explains the CREATE MATERIALIZED VIEW AS SELECT T-SQL statement in Azure SQL Data Warehouse for developing solutions. The article also provides code examples.

A Materialized View persists the data returned from the view definition query and automatically gets updated as data changes in the underlying tables. It improves the performance of complex queries (typically queries with joins and aggregations) while offering simple maintenance operations. With its execution plan automatching capability, a materialized view does not have to be referenced in the query for the optimizer to consider the view for substitution. This allows data engineers to implement materialized views as a mechanism for improving query response time, without having to change queries.

Topic link icon Transact-SQL Syntax Conventions

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.

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.

When MIN/MAX aggregates are used in the SELECT list of materialized view definition, following requirements apply:

  • FOR_APPEND is required. For example:

    CREATE MATRIALIZED VIEW mv_test2  
    WITH (distribution = hash(i_category_id), FOR_APPEND)  
    AS
    SELECT MAX(i.i_rec_start_date) as max_i_rec_start_date, MIN(i.i_rec_end_date) as min_i_rec_end_date, i.i_item_sk, i.i_item_id, i.i_category_id
    FROM syntheticworkload.item i  
    GROUP BY i.i_item_sk, i.i_item_id, i.i_category_id
    
  • The materialized view will be disabled when an UPDATE or DELETE occurs in the referenced base tables.  This restriction does not apply to INSERTs.  To re-enable the materialized view, run ALTER MATERIALIZED INDEX with REBUILD.

Remarks

A materialized view in Azure data warehouse is very similar to an indexed view in SQL Server.  It shares almost the same restrictions as indexed view (see Create Indexed Views for details) except that a materialized view supports aggregate functions.   Here are additional considerations for materialized view.

Only CLUSTERED COLUMNSTORE INDEX is supported by materialized view.

A materialized view can be dropped via DROP VIEW. You can use ALTER MATERIALIZED VIEW to disable or rebuild a materialized view.

Materialized Views can be created on partitioned tables.  SPLIT/MERGE operations are supported on tables referenced in materialized views.  SWITCH is not supported on tables referenced in materialized views. If attempted, the user will see the error, Msg 106104, Level 16, State 1, Line 9

ALTER TABLE SWITCH is not supported on tables that are referenced in materialized views. Disable or drop the materialized views before using ALTER TABLE SWITCH. In the following scenarios, the materialized view creation requires new columns to be added to the materialized view:

Scenario New columns to add to materialized view Comment
COUNT_BIG() is missing in the SELECT list of an materialized view definition COUNT_BIG (*) Automatically added by materialized view creation. No user action is required.
SUM(a) is specified by users in the SELECT list of an materialized view definition AND ‘a’ is a nullable expression COUNT_BIG (a) Users need to add the expression ‘a’ manually in the materialized view definition.
AVG(a) is specified by users in the SELECT list of an materialized view definition where ‘a’ is an expression. SUM(a), COUNT_BIG(a) Automatically added by materialized view creation. No user action is required.
STDEV(a) is specified by users in the SELECT list of an materialized view definition where ‘a’ is an expression. SUM(a), COUNT_BIG(a), SUM(square(a)) Automatically added by materialized view creation. No user action is required.

Once created, materialized views are visible within SQL Server Management Studio under the views folder of the Azure SQL Data Warehouse instance.

Users can run SP_SPACEUSED and DBCC PDW_SHOWSPACEUSED to determine the space being consumed by an materialized view.

EXPLAIN plan and the graphical Estimated Execution Plan in SQL Server Management Studio can show whether a materialized view is considered by the query optimizer for query execution. and the graphical Estimated Execution Plan in SQL Server Management Studio can show whether a materialized view is considered by the query optimizer for query execution.

To find out if a SQL statement can benefit from a new materialized view, run the EXPLAIN command with WITH_RECOMMENDATIONS. For details, see EXPLAIN (Transact-SQL).

Permissions

Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.

See also

ALTER MATERIALIZED VIEW (Transact-SQL)
EXPLAIN (Transact-SQL)
sys.pdw_materialized_view_column_distribution_properties (Transact-SQL)
sys.pdw_materialized_view_distribution_properties (Transact-SQL)
sys.pdw_materialized_view_mappings (Transact-SQL)
DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL)
SQL Data Warehouse and Parallel Data Warehouse Catalog Views
System views supported in Azure SQL Data Warehouse
T-SQL statements supported in Azure SQL Data Warehouse