Materialised View

Steven RogerJune 14, 2024

0 11 2 minutes read

Materialised View will persist the data from the database table which will return from the view definition questions and it will automatically gets updated as the data keeps on changes in tables, this will improve the performance of the queries when it’s giving simple maintenance operations.

This is an automatic query which will optimise the mechanism. Here users will not question materialised views directly so whenever the user will be submitting then the engine will ask for the users permission to the query engine and fails the query without execution.

Suppose if the user don’t have access to the database of tables or regular views in query, the users permission will be verified then the optimiser automatically uses matching materialised view to execute the query for the quick performance.

Table of Contents

Syntax is been given by:

Create materialised view [ ] materialise view name

With(

)

As < select statement>

<Distribution option> ::=

}

Select select criteria

Argument

Schema name: this is the schema to which view will be belonging

Materialised view name: this is the view name which will follow rules for identifiers and specifying the view owner where name is optional.

Distribution option: only the hash and round robin distribution will be supported

Select statement: this will select list in materialised view definition

Select list will include aggregate function

Group by will be used in the materialised view where all columns in the group by are included in select list.

Aggregate function: this will require in the select statement list of materialised view.

When the min or max aggregate will be used in select view then the syntax will be given by:

Create materialised view mb

With ( distribution = hash( I category id) for append)

Select max( start date) as max rec start date , min(end date) as min end date, i.i item sk, i.i category

From syntheticworkload. Item

Group by i.item sk, i.iiten I’d, i.i category;

Materialised view will not be generated in the table with the dynamic data masking which is also known as DDM. Suppose if the DDM column will not be the part of the materialised view and suppose in the table, column part is active materialised view will be not allowed and here DDM cannot be added.

This view can be created on the table where partition split/merge will be supported on the materialised views tables.

And once the materialised views are visible within SQL server management then the views folder of azure synapse instance.