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.
Syntax is been given by:
Create materialised view [ ] materialise view name
With(
)
As < select statement>
<Distribution option> ::=
}
<Select statement >
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)
As
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.
Ownership
1. This view wont be generated if the owner of the base tables and the materialised view to be created are not same.
2. Materialised view table will be residing in the different schemas.
3. When ever the materialised view will be created then the view ownership cannot be changed.
Permission
User will follow some of the permission to create the materialised view in addition to the meeting objects:
1. Create view permission in database table
2. We need to opt the permission on the tables of materialised view table
3. Reference permission
4. Alter permission
Advantages
1. This will make lesser the execution time for the complex queries with joins and aggregate functions
2. More the complex query then higher the potential for execution time saving.
3. Optimiser will be carry out to the SQL pool which will quickly use to locate the materialised views which will improve query execution plans
4. Data in the materialised view tables will be distributed differently from the base tables
Questions:
1. What is Materialised view? Explain
2. What are advantages of Materialised view?
3. What is Materialised view? Explain
4. What is Permission? Explain
