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

S’applique à :Applies to: ouiAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse AnalyticsS’applique à :Applies to: ouiAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

Cet article explique l’instruction SQL CREATE MATERIALIZED VIEW AS SELECT T dans Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) pour développer des solutions.This article explains the CREATE MATERIALIZED VIEW AS SELECT T-SQL statement in Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) for developing solutions. L’article fournit également des exemples de code.The article also provides code examples.

Une vue matérialisée conserve les données renvoyées par la requête de définition de vue et est automatiquement mise à jour à mesure que les données changent dans les tables sous-jacentes.A Materialized View persists the data returned from the view definition query and automatically gets updated as data changes in the underlying tables. Elle améliore les performances des requêtes complexes (généralement des requêtes contenant des jointures et agrégations) tout en offrant des opérations de maintenance simples.It improves the performance of complex queries (typically queries with joins and aggregations) while offering simple maintenance operations. Avec sa capacité d’automatching du plan d’exécution, un affichage matérialisé ne devra pas être référencés dans la requête pour que l’optimiseur prenne en compte l’affichage pour substitution.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. Cette fonctionnalité permet aux ingénieurs des données d’implémenter des vues matérialisées comme un mécanisme pour améliorer les temps de réponse des requêtes, sans devoir modifier les requêtes.This capability allows data engineers to implement materialized views as a mechanism for improving query response time, without having to change queries.

Icône du lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

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

Notes

Cette syntaxe n’est pas prise en charge par le pool SQL serverless (préversion) dans Azure Synapse Analytics.This syntax is not supported by serverless SQL pool (preview) in Azure Synapse Analytics.

ArgumentsArguments

schema_name schema_name
Nom du schéma auquel appartient la vue.Is the name of the schema to which the view belongs.

materialized_view_name materialized_view_name
Nom de la vue.Is the name of the view. Les noms des vues doivent se conformer aux règles applicables aux identificateurs.View names must follow the rules for identifiers. Vous n'êtes pas tenu de spécifier le nom du propriétaire de la vue.Specifying the view owner name is optional.

option de distribution distribution option
Seules les distributions HASH et ROUND_ROBIN sont prises en charge.Only HASH and ROUND_ROBIN distributions are supported.

select_statement select_statement
La liste SELECT dans la définition de l’affichage matérialisé doit respecter au moins un de ces deux critères :The SELECT list in the materialized view definition needs to meet at least one of these two criteria:

  • La liste SELECT contient une fonction d’agrégation.The SELECT list contains an aggregate function.
  • GROUP BY est utilisé dans la définition de l’affichage matérialisé et toutes les colonnes dans GROUP BY sont incluses dans la liste SELECT.GROUP BY is used in the Materialized view definition and all columns in GROUP BY are included in the SELECT list. Il est possible d’utiliser jusqu’à 32 colonnes dans la clause GROUP BY.Up to 32 columns can be used in the GROUP BY clause.

Les fonctions d’agrégation sont requises dans la liste SELECT de la définition de l’affichage matérialisé.Aggregate functions are required in the SELECT list of the materialized view definition. Les agrégations prises en charge incluent MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.Supported aggregations include MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

Lorsque les agrégats MIN/MAX sont utilisés dans la liste SELECT de la définition de l’affichage matérialisé, les conditions suivantes s’appliquent :When MIN/MAX aggregates are used in the SELECT list of materialized view definition, following requirements apply:

  • FOR_APPEND est requis.FOR_APPEND is required. Par exemple :For example:

    CREATE MATERIALIZED 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
    
  • L’affichage matérialisé sera désactivé lorsque UPDATE ou DELETE se produit dans les tables de base référencées.The materialized view will be disabled when an UPDATE or DELETE occurs in the referenced base tables.  Cette restriction ne s’applique pas aux instructions INSERT.  This restriction doesn't apply to INSERTs.  Pour réactiver l’affichage matérialisé, exécutez ALTER MATERIALIZED VIEW avec REBUILD.  To re-enable the materialized view, run ALTER MATERIALIZED VIEW with REBUILD.

NotesRemarks

Une vue matérialisée dans l’entrepôt de données Azure est similaire à une vue indexée dans SQL Server.A materialized view in Azure data warehouse is similar to an indexed view in SQL Server.Il partage presque les mêmes restrictions que la vue indexée (consultez Créer des vues indexées pour plus d’informations), à ceci près qu’un affichage matérialisé prend en charge des fonctions d’agrégation.  It shares almost the same restrictions as indexed view (see Create Indexed Views for details) except that a materialized view supports aggregate functions.  

Notes

Bien que CREATE MATERIALIZED VIEW ne prenne pas en charge COUNT, DISTINCT, COUNT(DISTINCT expression) ou COUNT_BIG (DISTINCT expression), les requêtes SELECT avec ces fonctions peuvent tirer parti des vues matérialisées afin d’améliorer les performances, car l’optimiseur Synapse SQL peut réécrire automatiquement ces agrégations dans la requête utilisateur pour les faire correspondre à des vues matérialisées existantes.Although CREATE MATERIALIZED VIEW does not support COUNT, DISTINCT, COUNT(DISTINCT expression), or COUNT_BIG (DISTINCT expression), SELECT queries with these functions can still benefit from materialized views for faster performance as the Synapse SQL optimizer can automatically re-write those aggregations in the user query to match existing materialized views. Pour plus d’informations, consultez la section Exemple de cet article.For details, check this article's example section.

APPROX_COUNT_DISTINCT n’est pas pris en charge dans CREATE MATERIALIZED VIEW AS SELECT.APPROX_COUNT_DISTINCT is not supported in CREATE MATERIALIZED VIEW AS SELECT.

Seul CLUSTERED COLUMNSTORE INDEX est pris en charge par l’affichage matérialisé.Only CLUSTERED COLUMNSTORE INDEX is supported by materialized view.

Une vue matérialisée ne peut pas référencer d’autres vues.A materialized view cannot reference other views.

Une vue matérialisée ne peut pas être créée sur une table avec un masquage dynamique des données, même si la colonne avec masquage dynamique des données ne fait pas partie de la vue matérialisée.A materialized view can't be created on a table with dynamic data masking (DDM), even if the DDM column is not part of the materialized view. Si une colonne de table fait partie d’une vue matérialisée active ou d’une vue matérialisée désactivée, le masquage dynamique des données ne peut pas être ajouté à cette colonne.If a table column is part of an active materialized view or a disabled materialized view, DDM can't be added to this column.

Vous ne pouvez pas créer une vue matérialisée sur une table pour laquelle la sécurité au niveau des lignes est activée.A materialized view can't be created on a table with row level security enabled.

Les affichages matérialisés peuvent être créés sur les tables partitionnées.Materialized Views can be created on partitioned tables.  Les opérations SPLIT/MERGE sur les partitions sont prises en charge sur les tables de base des vues matérialisées ; l’opération SWITCH sur des partitions n’est pas prise en charge.  Partition SPLIT/MERGE are supported on materialized views base tables, partition SWITCH isn't supported.

ALTER TABLE SWITCH n’est pas pris en charge sur les tables référencées dans les affichages matérialisés.ALTER TABLE SWITCH is not supported on tables that are referenced in materialized views. Désactiver ou déposer les affichages matérialisés avant d’utiliser ALTER TABLE SWITCH.Disable or drop the materialized views before using ALTER TABLE SWITCH. Dans les scénarios suivants, la création de l’affichage matérialisé nécessite l’ajout de nouvelles colonnes à l’affichage matérialisé :In the following scenarios, the materialized view creation requires new columns to be added to the materialized view:

ScénarioScenario Nouvelles colonnes à ajouter à l’affichage matérialiséNew columns to add to materialized view CommentaireComment
COUNT_BIG() est manquant dans la liste SELECT d’une définition de vue matérialiséeCOUNT_BIG() is missing in the SELECT list of a materialized view definition COUNT_BIG (*)COUNT_BIG (*) Ajouté automatiquement par la création de l’affichage matérialisé.Automatically added by materialized view creation. Aucune action de l'utilisateur n'est requise.No user action is required.
SUM(a) est spécifié par les utilisateurs dans la liste SELECT d’une définition d’affichage matérialisé ET « a » est une expression nullableSUM(a) is specified by users in the SELECT list of a materialized view definition AND 'a' is a nullable expression COUNT_BIG (a)COUNT_BIG (a) Les utilisateurs doivent ajouter l’expression « a » manuellement dans la définition de l’affichage matérialisé.Users need to add the expression 'a' manually in the materialized view definition.
AVG(a) est spécifié par les utilisateurs dans la liste SELECT d’une définition d’affichage matérialisé où « a » est une expression.AVG(a) is specified by users in the SELECT list of a materialized view definition where 'a' is an expression. SUM(a), COUNT_BIG(a)SUM(a), COUNT_BIG(a) Ajouté automatiquement par la création de l’affichage matérialisé.Automatically added by materialized view creation. Aucune action de l'utilisateur n'est requise.No user action is required.
STDEV(a) est spécifié par les utilisateurs dans la liste SELECT d’une définition d’affichage matérialisé où « a » est une expression.STDEV(a) is specified by users in the SELECT list of a materialized view definition where 'a' is an expression. SUM(a), COUNT_BIG(a), SUM(square(a))SUM(a), COUNT_BIG(a), SUM(square(a)) Ajouté automatiquement par la création de l’affichage matérialisé.Automatically added by materialized view creation. Aucune action de l'utilisateur n'est requise.No user action is required.

Après la création, des affichages matérialisés sont visibles dans SQL Server Management Studio sous le dossier des affichages de l’instance Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse).Once created, materialized views are visible within SQL Server Management Studio under the views folder of the Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) instance.

Les utilisateurs peuvent exécuter SP_SPACEUSED et DBCC PDW_SHOWSPACEUSED pour déterminer l’espace consommé par une vue matérialisée.Users can run SP_SPACEUSED and DBCC PDW_SHOWSPACEUSED to determine the space being consumed by a materialized view.

Un affichage matérialisé peut être déposé par le biais de DROP VIEW.A materialized view can be dropped via DROP VIEW. Vous pouvez utiliser ALTER MATERIALIZED VIEW pour désactiver ou régénérer un affichage matérialisé.You can use ALTER MATERIALIZED VIEW to disable or rebuild a materialized view.

Le plan EXPLAIN et le Plan d’exécution graphique estimé dans SQL Server Management Studio peuvent indiquer si un affichage matérialisé est pris en compte par l’optimiseur de requête pour l’exécution des requêtes.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. et le Plan d’exécution graphique estimé dans SQL Server Management Studio peuvent indiquer si un affichage matérialisé est pris en compte par l’optimiseur de requête pour l’exécution des requêtes.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.

Pour déterminer si une instruction SQL peut bénéficier d’un nouvel affichage matérialisé, exécutez la commande EXPLAIN avec WITH_RECOMMENDATIONS.To find out if a SQL statement can benefit from a new materialized view, run the EXPLAIN command with WITH_RECOMMENDATIONS. Pour plus d’informations, consultez EXPLAIN (Transact-SQL).For details, see EXPLAIN (Transact-SQL).

AutorisationsPermissions

Nécessite l’autorisation 1) REFERENCES et CREATE VIEW OU 2) CONTROL sur le schéma dans lequel la vue est créée.Requires 1) REFERENCES and CREATE VIEW permission OR 2) CONTROL permission on the schema in which the view is being created.

ExempleExample

R.A. Cet exemple montre comment l’optimiseur Synapse SQL utilise automatiquement des vues matérialisées pour exécuter une requête afin d’améliorer les performances, même quand la requête utilise des fonctions non prises en charge dans CREATE MATERIALIZED VIEW, telles que COUNT(DISTINCT expression).This example shows how Synapse SQL optimizer automatically uses materialized views to execute a query for better performance even when the query uses functions un-supported in CREATE MATERIALIZED VIEW, such as COUNT(DISTINCT expression). Une requête utilisateur dont l’exécution prenait habituellement plusieurs secondes dure désormais moins d’une seconde, sans qu’elle doive subir de modification.A query used to take multiple seconds to complete now finishes in sub-second without any change in the user query.


-- Create a table with ~536 million rows
create table t(a int not null, b int not null, c int not null) with (distribution=hash(a), clustered columnstore index);

insert into t values(1,1,1);

declare @p int =1;
while (@P < 30)
    begin
    insert into t select a+1,b+2,c+3 from t;  
    select @p +=1;
end

-- A SELECT query with COUNT_BIG (DISTINCT expression) took multiple seconds to complete and it reads data directly from the base table a. 
select a, count_big(distinct b) from t group by a;

-- Create two materialized views, not using COUNT_BIG(DISTINCT expression).
create materialized view V1 with(distribution=hash(a)) as select a, b from dbo.t group by a, b;

-- Clear all cache.

DBCC DROPCLEANBUFFERS;
DBCC freeproccache;

-- Check the estimated execution plan in SQL Server Management Studio.  It shows the SELECT query is first step (GET operator) is to read data from the materialized view V1, not from base table a.
select a, count_big(distinct b) from t group by a;

-- Now execute this SELECT query.  This time it took sub-second to complete because Synapse SQL engine automatically matches the query with materialized view V1 and uses it for faster query execution.  There was no change in the user query.

DECLARE @timerstart datetime2, @timerend datetime2;
SET @timerstart = sysdatetime();

select a, count_big(distinct b) from t group by a;

SET @timerend = sysdatetime()
select DATEDIFF(ms,@timerstart,@timerend);

Voir aussiSee also

Réglage des performances avec une vue matérialisée Performance tuning with Materialized View
ALTER MATERIALIZED VIEW (Transact-SQL) ALTER MATERIALIZED VIEW (Transact-SQL)
DROP VIEWDROP VIEW
EXPLAIN (Transact-SQL) EXPLAIN (Transact-SQL)
sys.pdw_materialized_view_column_distribution_properties (Transact-SQL) sys.pdw_materialized_view_column_distribution_properties (Transact-SQL)
sys.pdw_materialized_view_distribution_properties (Transact-SQL) sys.pdw_materialized_view_distribution_properties (Transact-SQL)
sys.pdw_materialized_view_mappings (Transact-SQL) sys.pdw_materialized_view_mappings (Transact-SQL)
DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL) DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL)
Vues de catalogue Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data Warehouse Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse Catalog Views
Vues système prises en charge dans Azure Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) System views supported in Azure Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse)
Instructions T-SQL prises en charge dans Azure Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse)T-SQL statements supported in Azure Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse)