CREATE TABLE AS SELECT (Azure Synapse Analytics)CREATE TABLE AS SELECT (Azure Synapse Analytics)

S’applique à :Applies to: ouiAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics ouiParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data WarehouseS’applique à :Applies to: ouiAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics ouiParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse

CREATE TABLE AS SELECT (CTAS) est l’une des fonctionnalités de T-SQL les plus importantes.CREATE TABLE AS SELECT (CTAS) is one of the most important T-SQL features available. Il s’agit d’une opération entièrement parallélisée qui crée une table en fonction de la sortie d’une instruction SELECT.It is a fully parallelized operation that creates a new table based on the output of a SELECT statement. CTAS offre le moyen le plus simple et le plus rapide de créer une copie de table.CTAS is the simplest and fastest way to create a copy of a table.

Par exemple, CTAS vous permet d’effectuer les opérations suivantes :For example, use CTAS to:

  • Recréer une table avec une colonne de distribution de hachage différente.Re-create a table with a different hash distribution column.
  • Recréer une table comme étant répliquée.Re-create a table as replicated.
  • Créer un index columnstore uniquement sur certaines colonnes de la table.Create a columnstore index on just some of the columns in the table.
  • Interroger ou importer des données externes.Query or import external data.

Notes

CTAS s’ajoute aux fonctionnalités de création de table. Ainsi, au lieu de répéter le contenu de la rubrique CREATE TABLE,Since CTAS adds to the capabilities of creating a table, this topic tries not to repeat the CREATE TABLE topic. cette rubrique décrit les différences entre les instructions CTAS et CREATE TABLE.Instead, it describes the differences between the CTAS and CREATE TABLE statements. Pour le détail de CREATE TABLE, consultez l’état de CREATE TABLE (Azure Synapse Analytics).For the CREATE TABLE details, see CREATE TABLE (Azure Synapse Analytics) statement.

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

SyntaxeSyntax

CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ( column_name [ ,...n ] ) ]  
    WITH ( 
      <distribution_option> -- required
      [ , <table_option> [ ,...n ] ]    
    )  
    AS <select_statement>  
    OPTION <query_hint> 
[;]  

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

<table_option> ::= 
    {   
        CLUSTERED COLUMNSTORE INDEX --default for Synapse Analytics 
      | CLUSTERED COLUMNSTORE INDEX ORDER (column[,...n])
      | HEAP --default for Parallel Data Warehouse   
      | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) --default is ASC 
    }  
      | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] --default is LEFT  
        FOR VALUES ( [ boundary_value [,...n] ] ) ) 
  
<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT select_criteria  

<query_hint> ::=
    {
        MAXDOP 
    }

ArgumentsArguments

Pour plus d’informations, consultez la section Arguments de la rubrique CREATE TABLE.For details, see the Arguments section in CREATE TABLE.

Options de colonneColumn options

column_name [ ,...n ]column_name [ ,...n ]
Les noms de colonne n’autorisent pas les options de colonne mentionnées dans CREATE TABLE.Column names do not allow the column options mentioned in CREATE TABLE. À la place, vous pouvez fournir une liste facultative d’un ou plusieurs noms de colonne pour la nouvelle table.Instead, you can provide an optional list of one or more column names for the new table. Les colonnes de la nouvelle table prennent alors les noms que vous spécifiez.The columns in the new table will use the names you specify. Quand vous spécifiez des noms de colonne, le nombre de colonnes figurant dans la liste de colonnes doit correspondre au nombre de colonnes figurant dans les résultats de l’instruction select.When you specify column names, the number of columns in the column list must match the number of columns in the select results. Si vous ne spécifiez pas de noms de colonne, la nouvelle table cible utilise les noms de colonne figurant dans les résultats de l’instruction select.If you don't specify any column names, the new target table will use the column names in the select statement results.

Vous ne pouvez spécifier aucune autre option de colonne comme les types de données, le classement ou la possibilité de valeur NULL.You cannot specify any other column options such as data types, collation, or nullability. Chacun de ces attributs est dérivé des résultats de l’instruction SELECT.Each of these attributes is derived from the results of the SELECT statement. Cependant, vous pouvez utiliser l’instruction SELECT pour modifier les attributs.However, you can use the SELECT statement to change the attributes. Pour obtenir un exemple, consultez Utiliser CTAS pour modifier des attributs de colonne.For an example, see Use CTAS to change column attributes.

Options de distribution de tableTable distribution options

DISTRIBUTION = HASH ( distribution_column_name ) | ROUND_ROBIN | REPLICATEDISTRIBUTION = HASH ( distribution_column_name ) | ROUND_ROBIN | REPLICATE
L’instruction CTAS nécessite une option de distribution et n’a pas de valeurs par défaut.The CTAS statement requires a distribution option and does not have default values. Elle se distingue en cela de CREATE TABLE qui en possède.This is different from CREATE TABLE which has defaults.

Pour savoir comment choisir la colonne de distribution la plus appropriée, consultez la section Options de distribution de table de la rubrique CREATE TABLE.For details and to understand how to choose the best distribution column, see the Table distribution options section in CREATE TABLE.

Options de partition de tableTable partition options

L’instruction CTAS crée par défaut une table non partitionnée, même si la table source est partitionnée.The CTAS statement creates a non-partitioned table by default, even if the source table is partitioned. Pour créer une table partitionnée avec l’instruction CTAS, vous devez spécifier l’option de partition.To create a partitioned table with the CTAS statement, you must specify the partition option.

Pour plus d’informations, consultez la section Options de partition de table de la rubrique CREATE TABLE.For details, see the Table partition options section in CREATE TABLE.

Select (instruction)Select statement

L’instruction select représente la différence fondamentale entre CTAS et CREATE TABLE.The select statement is the fundamental difference between CTAS and CREATE TABLE.

WITH common_table_expressionWITH common_table_expression
Spécifie un jeu de résultats nommé temporaire, désigné par le terme d'expression de table commune (CTE, Common Table Expression).Specifies a temporary named result set, known as a common table expression (CTE). Pour plus d’informations, consultez WITH common_table_expression (Transact-SQL).For more information, see WITH common_table_expression (Transact-SQL).

SELECT select_criteriaSELECT select_criteria
Remplit la nouvelle table avec les résultats d’une instruction SELECT.Populates the new table with the results from a SELECT statement. select_criteria correspond au corps de l’instruction SELECT qui détermine les données qui sont copiées dans la nouvelle table.select_criteria is the body of the SELECT statement that determines which data to copy to the new table. Pour plus d’informations sur les instructions SELECT, consultez SELECT (Transact-SQL).For information about SELECT statements, see SELECT (Transact-SQL).

Indicateur de requêteQuery hint

Les utilisateurs peuvent définir MAXDOP sur une valeur entière pour contrôler le degré maximal de parallélisme.Users can set MAXDOP to an integer value to control the maximum degree of parallelism. Quand MAXDOP a la valeur 1, la requête est exécutée par un seul thread.When MAXDOP is set to 1, the query is executed by a single thread.

AutorisationsPermissions

CTAS exige une autorisation SELECT sur les objets référencés dans select_criteria.CTAS requires SELECT permission on any objects referenced in the select_criteria.

Pour plus d’informations sur les autorisations permettant de créer une table, consultez Autorisations dans la rubrique CREATE TABLE.For permissions to create a table, see Permissions in CREATE TABLE.

Remarques d'ordre généralGeneral Remarks

Pour plus d’informations, consultez Remarques d’ordre général dans la rubrique CREATE TABLE.For details, see General Remarks in CREATE TABLE.

Limitations et restrictionsLimitations and Restrictions

Un index Columnstore en cluster ordonné peut être créé sur les colonnes de tout type de données pris en charge dans Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) à l’exception des colonnes de type chaîne.An ordered clustered columnstore index can be created on columns of any data types supported in Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) except for string columns.

SET ROWCOUNT (Transact-SQL) ne produit aucun effet sur CTAS.SET ROWCOUNT (Transact-SQL) has no effect on CTAS. Pour obtenir un comportement similaire, utilisez TOP (Transact-SQL).To achieve a similar behavior, use TOP (Transact-SQL).

Pour plus d’informations, consultez Limitations et restrictions dans la rubrique CREATE TABLE.For details, see Limitations and Restrictions in CREATE TABLE.

Comportement de verrouillageLocking Behavior

Pour plus d’informations, consultez Comportement de verrouillage dans la rubrique CREATE TABLE.For details, see Locking Behavior in CREATE TABLE.

PerformancesPerformance

Pour une table de hachage distribuée, CTAS vous permet de choisir une colonne de distribution différente pour bénéficier de meilleures performances pour les jointures et les agrégations.For a hash-distributed table, you can use CTAS to choose a different distribution column to achieve better performance for joins and aggregations. Si votre objectif n’est pas de choisir une colonne de distribution différente, vous bénéficierez de meilleures performances avec CTAS si vous spécifiez la même colonne de distribution, car vous éviterez ainsi une redistribution des lignes.If choosing a different distribution column is not your goal, you will have the best CTAS performance if you specify the same distribution column since this will avoid re-distributing the rows.

Si vous utilisez CTAS pour créer une table et que les performances ne sont pas un facteur déterminant, vous pouvez spécifier ROUND_ROBIN pour éviter d’avoir à choisir une colonne de distribution.If you are using CTAS to create table and performance is not a factor, you can specify ROUND_ROBIN to avoid having to decide on a distribution column.

Pour éviter un déplacement de données dans les requêtes suivantes, vous pouvez spécifier REPLICATE au prix d’un stockage accru pour charger une copie complète de la table sur chaque nœud de calcul.To avoid data movement in subsequent queries, you can specify REPLICATE at the cost of increased storage for loading a full copy of the table on each Compute node.

Exemples de copie d’une tableExamples for copying a table

R.A. Utiliser CTAS pour copier une tableUse CTAS to copy a table

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

L’une des utilisations les plus courantes de CTAS est peut-être celle qui consiste à créer une copie d’une table dans le but de pouvoir modifier le langage de définition de données (DDL).Perhaps one of the most common uses of CTAS is creating a copy of a table so that you can change the DDL. Si par exemple vous avez créé au départ une table de type ROUND_ROBIN et que vous voulez maintenant la modifier pour en faire une table distribuée sur une colonne, CTAS est la méthode qui vous permettra de modifier la colonne de distribution.If for example you originally created your table as ROUND_ROBIN and now want change it to a table distributed on a column, CTAS is how you would change the distribution column. CTAS permet aussi de modifier le partitionnement, l’indexation ou les types de colonnes.CTAS can also be used to change partitioning, indexing, or column types.

Supposons que vous avez créé cette table en utilisant le type de distribution par défaut ROUND_ROBIN sachant qu’aucune colonne de distribution n’était spécifiée dans l’instruction CREATE TABLE.Let's say you created this table using the default distribution type of ROUND_ROBIN distributed since no distribution column was specified in the CREATE TABLE.

CREATE TABLE FactInternetSales
(
    ProductKey INT NOT NULL,
    OrderDateKey INT NOT NULL,
    DueDateKey INT NOT NULL,
    ShipDateKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    PromotionKey INT NOT NULL,
    CurrencyKey INT NOT NULL,
    SalesTerritoryKey INT NOT NULL,
    SalesOrderNumber NVARCHAR(20) NOT NULL,
    SalesOrderLineNumber TINYINT NOT NULL,
    RevisionNumber TINYINT NOT NULL,
    OrderQuantity SMALLINT NOT NULL,
    UnitPrice MONEY NOT NULL,
    ExtendedAmount MONEY NOT NULL,
    UnitPriceDiscountPct FLOAT NOT NULL,
    DiscountAmount FLOAT NOT NULL,
    ProductStandardCost MONEY NOT NULL,
    TotalProductCost MONEY NOT NULL,
    SalesAmount MONEY NOT NULL,
    TaxAmt MONEY NOT NULL,
    Freight MONEY NOT NULL,
    CarrierTrackingNumber NVARCHAR(25),
    CustomerPONumber NVARCHAR(25)
);

Maintenant, vous voulez créer une copie de cette table avec un index cluster columnstore de façon à profiter des performances offertes par les tables cluster columnstore.Now you want to create a new copy of this table with a clustered columnstore index so that you can take advantage of the performance of clustered columnstore tables. Vous voulez aussi distribuer cette table sur ProductKey, car des jointures sont prévues dans cette colonne et vous souhaitez éviter que des données soient déplacées à ces occasions.You also want to distribute this table on ProductKey since you are anticipating joins on this column and want to avoid data movement during joins on ProductKey. En dernier lieu, vous souhaitez aussi ajouter le partitionnement à OrderDateKey de façon à pouvoir supprimer rapidement les anciennes données en éliminant les anciennes partitions.Lastly you also want to add partitioning on OrderDateKey so that you can quickly delete old data by dropping old partitions. Voici l’instruction CTAS permettant de copier l’ancienne table dans une nouvelle.Here is the CTAS statement which would copy your old table into a new table.

CREATE TABLE FactInternetSales_new
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH(ProductKey),
    PARTITION
    (
        OrderDateKey RANGE RIGHT FOR VALUES
        (
        20000101,20010101,20020101,20030101,20040101,20050101,20060101,20070101,20080101,20090101,
        20100101,20110101,20120101,20130101,20140101,20150101,20160101,20170101,20180101,20190101,
        20200101,20210101,20220101,20230101,20240101,20250101,20260101,20270101,20280101,20290101
        )
    )
)
AS SELECT * FROM FactInternetSales;

Enfin, vous pouvez renommer vos tables pour spécifier la nouvelle table et supprimer l’ancienne.Finally you can rename your tables to swap in your new table and then drop your old table.

RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;

DROP TABLE FactInternetSales_old;

Exemples d’options de colonneExamples for column options

B.B. Utiliser CTAS pour modifier des attributs de colonneUse CTAS to change column attributes

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

Cet exemple utilise CTAS pour modifier des types de données, la possibilité de valeur NULL et le classement pour plusieurs colonnes de la table DimCustomer2.This example uses CTAS to change data types, nullability, and collation for several columns in the DimCustomer2 table.

-- Original table 
CREATE TABLE [dbo].[DimCustomer2] (  
    [CustomerKey] INT NOT NULL,  
    [GeographyKey] INT NULL,  
    [CustomerAlternateKey] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  
)  
WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([CustomerKey]));  
  
-- CTAS example to change data types, nullability, and column collations  
CREATE TABLE test  
WITH (HEAP, DISTRIBUTION = ROUND_ROBIN)  
AS  
SELECT  
    CustomerKey AS CustomerKeyNoChange,  
    CustomerKey*1 AS CustomerKeyChangeNullable,  
    CAST(CustomerKey AS DECIMAL(10,2)) AS CustomerKeyChangeDataTypeNullable,  
    ISNULL(CAST(CustomerKey AS DECIMAL(10,2)),0) AS CustomerKeyChangeDataTypeNotNullable,  
    GeographyKey AS GeographyKeyNoChange,  
    ISNULL(GeographyKey,0) AS GeographyKeyChangeNotNullable,  
    CustomerAlternateKey AS CustomerAlternateKeyNoChange,  
    CASE WHEN CustomerAlternateKey = CustomerAlternateKey 
        THEN CustomerAlternateKey END AS CustomerAlternateKeyNullable,  
    CustomerAlternateKey COLLATE Latin1_General_CS_AS_KS_WS AS CustomerAlternateKeyChangeCollation  
FROM [dbo].[DimCustomer2]  
  
-- Resulting table 
CREATE TABLE [dbo].[test] (
    [CustomerKeyNoChange] INT NOT NULL, 
    [CustomerKeyChangeNullable] INT NULL, 
    [CustomerKeyChangeDataTypeNullable] DECIMAL(10, 2) NULL, 
    [CustomerKeyChangeDataTypeNotNullable] DECIMAL(10, 2) NOT NULL, 
    [GeographyKeyNoChange] INT NULL, 
    [GeographyKeyChangeNotNullable] INT NOT NULL, 
    [CustomerAlternateKeyNoChange] NVARCHAR(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
    [CustomerAlternateKeyNullable] NVARCHAR(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
    [CustomerAlternateKeyChangeCollation] NVARCHAR(15) COLLATE Latin1_General_CS_AS_KS_WS NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN);

En dernier lieu, vous pouvez utiliser RENAME (Transact-SQL) pour permuter les noms de tables.As a final step, you can use RENAME (Transact-SQL) to switch the table names. DimCustomer2 devient ainsi la nouvelle table.This makes DimCustomer2 be the new table.

RENAME OBJECT DimCustomer2 TO DimCustomer2_old;
RENAME OBJECT test TO DimCustomer2;

DROP TABLE DimCustomer2_old;

Exemples de distribution de tableExamples for table distribution

C.C. Utiliser CTAS pour modifier la méthode de distribution d’une tableUse CTAS to change the distribution method for a table

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

Cet exemple simple montre comment modifier la méthode de distribution d’une table.This simple example shows how to change the distribution method for a table. Pour détailler la procédure, il transforme une table de hachage distribuée en table round robin (tourniquet), puis reconvertit cette dernière en table de hachage distribuée.To show the mechanics of how to do this, it changes a hash-distributed table to round-robin and then changes the round-robin table back to hash distributed. La table finale correspond à la table d’origine.The final table matches the original table.

Dans la plupart des cas, il n’est pas nécessaire de transformer une table de hachage distribuée en table round robin.In most cases you won't need to change a hash-distributed table to a round-robin table. En revanche, vous serez plus souvent amené à transformer une table round robin en table de hachage distribuée.More often, you might need to change a round-robin table to a hash distributed table. Par exemple, vous pouvez décider dans un premier temps de charger une nouvelle table sous forme de table round robin pour dans un second temps la convertir en table de hachage distribuée pour bénéficier de meilleures performances de jointure.For example, you might initially load a new table as round-robin and then later move it to a hash-distributed table to get better join performance.

Cet exemple utilise l’exemple de base de données AdventureWorks.This example uses the AdventureWorksDW sample database. Pour charger la version Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse), consultez Charger les données d’échantillon dans Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse)To load the Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) version, see Load sample data into Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse)

-- DimSalesTerritory is hash-distributed.
-- Copy it to a round-robin table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

Ensuite, reconvertissez-la en table de hachage distribuée.Next, change it back to a hash distributed table.

-- You just made DimSalesTerritory a round-robin table.
-- Change it back to the original hash-distributed table. 
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH(SalesTerritoryKey) 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

D.D. Utiliser CTAS pour convertir une table en table répliquéeUse CTAS to convert a table to a replicated table

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

Cet exemple vaut pour la conversion de tables round robin ou de hachage distribuées en table répliquée.This example applies for converting round-robin or hash-distributed tables to a replicated table. Cet exemple précis va encore plus loin que la méthode précédente de modification du type de distribution.This particular example takes the previous method of changing the distribution type one step further. DimSalesTerritory étant une dimension et probablement une table de plus petite taille, vous pouvez choisir de la recréer sous forme de table répliquée pour éviter que des données soient déplacées au moment de la joindre à d’autres tables.Since DimSalesTerritory is a dimension and likely a smaller table, you can choose to re-create the table as replicated to avoid data movement when joining to other tables.

-- DimSalesTerritory is hash-distributed.
-- Copy it to a replicated table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = REPLICATE 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

E.E. Utiliser CTAS pour créer une table avec moins de colonnesUse CTAS to create a table with fewer columns

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

L’exemple suivant crée une table distribuée de type round robin nommée myTable (c, ln).The following example creates a round-robin distributed table named myTable (c, ln). La nouvelle table contient seulement deux colonnes.The new table only has two columns. Elle utilise les alias des colonnes dans l’instruction SELECT à la place des noms des colonnes.It uses the column aliases in the SELECT statement for the names of the columns.

CREATE TABLE myTable  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT CustomerKey AS c, LastName AS ln  
    FROM dimCustomer; 

Exemples d’indicateurs de requêteExamples for query hints

F.F. Utiliser un indicateur de requête avec CREATE TABLE AS SELECT (CTAS)Use a Query Hint with CREATE TABLE AS SELECT (CTAS)

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

Cette requête présente la syntaxe de base pour utiliser un indicateur de jointure de requête avec l’instruction CTAS.This query shows the basic syntax for using a query join hint with the CTAS statement. Une fois la requête envoyée, Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) applique la stratégie de jointure hachée au moment de générer le plan de requête pour chaque distribution individuelle.After the query is submitted, Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) applies the hash join strategy when it generates the query plan for each individual distribution. Pour plus d’informations sur l’indicateur de requête de jointure hachée, consultez Clause OPTION (Transact-SQL).For more information on the hash join query hint, see OPTION Clause (Transact-SQL).

CREATE TABLE dbo.FactInternetSalesNew  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN   
  )  
AS SELECT T1.* FROM dbo.FactInternetSales T1 JOIN dbo.DimCustomer T2  
ON ( T1.CustomerKey = T2.CustomerKey )  
OPTION ( HASH JOIN );  

Exemples de tables externesExamples for external tables

G.G. Utiliser CTAS pour importer des données à partir du stockage Blob AzureUse CTAS to import data from Azure Blob storage

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

Pour importer des données à partir d’une table externe, utilisez simplement CREATE TABLE AS SELECT pour effectuer une sélection dans la table externe.To import data from an external table, simply use CREATE TABLE AS SELECT to select from the external table. La syntaxe à utiliser pour sélectionner des données dans une table externe à destination de Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) est la même que celle permettant de sélectionner des données dans une table normale.The syntax to select data from an external table into Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) is the same as the syntax for selecting data from a regular table.

L’exemple suivant définit une table externe parmi des données situées dans un compte de stockage Blob Azure.The following example defines an external table on data in an Azure blob storage account. Il utilise ensuite CREATE TABLE AS SELECT pour effectuer une sélection dans la table externe.It then uses CREATE TABLE AS SELECT to select from the external table. Les données sont alors importées à partir de fichiers délimités par du texte dans le stockage Blob Azure, puis stockées dans une nouvelle table Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse).This imports the data from Azure blob storage text-delimited files and stores the data into a new Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) table.

--Use your own processes to create the text-delimited files on Azure blob storage.  
--Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url VARCHAR(50),  
    event_date DATE,  
    user_IP VARCHAR(50)  
)  
WITH (  
    LOCATION='/logs/clickstream/2015/',  
    DATA_SOURCE = MyAzureStorage,  
    FILE_FORMAT = TextFileFormat)  
;  
  
--Use CREATE TABLE AS SELECT to import the Azure blob storage data into a new   
--Synapse Analytics table called ClickStreamData  
CREATE TABLE ClickStreamData   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;  

H.H. Utiliser CTAS pour importer des données Hadoop à partir d’une table externeUse CTAS to import Hadoop data from an external table

S’applique à : Parallel Data WarehouseParallel Data WarehouseApplies to: Parallel Data WarehouseParallel Data Warehouse

Pour importer des données à partir d’une table externe, utilisez simplement CREATE TABLE AS SELECT pour effectuer une sélection dans la table externe.To import data from an external table, simply use CREATE TABLE AS SELECT to select from the external table. La syntaxe à utiliser pour sélectionner des données dans une table externe à destination de Parallel Data WarehouseParallel Data Warehouse est la même que celle permettant de sélectionner des données dans une table normale.The syntax to select data from an external table into Parallel Data WarehouseParallel Data Warehouse is the same as the syntax for selecting data from a regular table.

L’exemple suivant définit une table externe sur un cluster Hadoop.The following example defines an external table on a Hadoop cluster. Il utilise ensuite CREATE TABLE AS SELECT pour effectuer une sélection dans la table externe.It then uses CREATE TABLE AS SELECT to select from the external table. Les données sont alors importées à partir de fichiers délimités par du texte Hadoop, puis stockées dans une nouvelle table Parallel Data WarehouseParallel Data Warehouse.This imports the data from Hadoop text-delimited files and stores the data into a new Parallel Data WarehouseParallel Data Warehouse table.

-- Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url VARCHAR(50),  
    event_date DATE,  
    user_IP VARCHAR(50)  
)  
WITH (  
    LOCATION = 'hdfs://MyHadoop:5000/tpch1GB/employee.tbl',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|')  
)  
;  
  
-- Use your own processes to create the Hadoop text-delimited files 
-- on the Hadoop Cluster.  
  
-- Use CREATE TABLE AS SELECT to import the Hadoop data into a new 
-- table called ClickStreamPDW  
CREATE TABLE ClickStreamPDW   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;   

Exemples d’utilisation de CTAS pour remplacer du code SQL ServerExamples using CTAS to replace SQL Server code

CTAS permet de pallier l’absence de prise en charge de certaines fonctionnalités.Use CTAS to work around some unsupported features. En plus de permettre l’exécution de votre code dans l’entrepôt de données, le fait de réécrire le code existant pour utiliser CTAS aura généralement pour effet d’améliorer les performances.Besides being able to run your code on the data warehouse, rewriting existing code to use CTAS will usually improve performance. C’est le résultat de sa conception entièrement parallélisée.This is a result of its fully parallelized design.

Notes

Essayez de penser à CTAS en priorité.Try to think "CTAS first". Si vous pensez que vous pouvez résoudre un problème avec CTAS, c’est qu’il s’agit généralement de la meilleure façon de l’aborder, même si cela sous-entend d’écrire plus de données.If you think you can solve a problem using CTAS then that is generally the best way to approach it - even if you are writing more data as a result.

I.I. Utiliser CTAS plutôt que SELECT..INTOUse CTAS instead of SELECT..INTO

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

Le code SQL Server utilise généralement SELECT..INTO pour remplir une table avec les résultats d’une instruction SELECT.SQL Server code typically uses SELECT..INTO to populate a table with the results of a SELECT statement. Voici un exemple d’instruction SQL Server SELECT..INTO.This is an example of a SQL Server SELECT..INTO statement.

SELECT *
INTO    #tmp_fct
FROM    [dbo].[FactInternetSales]

Cette syntaxe n’est pas prise en charge dans Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data Warehouse.This syntax is not supported in Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data Warehouse. Cet exemple montre comment réécrire l’instruction SELECT..INTO précédente pour en faire une instruction CTAS.This example shows how to rewrite the previous SELECT..INTO statement as a CTAS statement. Vous pouvez choisir l’une des options DISTRIBUTION décrites dans la syntaxe CTAS.You can choose any of the DISTRIBUTION options described in the CTAS syntax. Cet exemple utilise la méthode de distribution ROUND_ROBIN.This example uses the ROUND_ROBIN distribution method.

CREATE TABLE #tmp_fct
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
;

J.J. Utiliser CTAS et des jointures implicites pour remplacer des jointures ANSI dans la clause FROM d’une instruction UPDATEUse CTAS and implicit joins to replace ANSI joins in the FROM clause of an UPDATE statement

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

Imaginez que vous êtes en présence d’une mise à jour complexe qui joint plus de deux tables et exécute l’instruction UPDATE ou DELETE à l’aide de la syntaxe de jointure ANSI.You may find you have a complex update that joins more than two tables together using ANSI joining syntax to perform the UPDATE or DELETE.

Vous devez mettre à jour cette table :Imagine you had to update this table:

CREATE TABLE [dbo].[AnnualCategorySales]
(   [EnglishProductCategoryName]    NVARCHAR(50)    NOT NULL
,   [CalendarYear]          SMALLINT    NOT NULL
,   [TotalSalesAmount]      MONEY       NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
;

La requête initiale peut se présenter comme suit :The original query might have looked something like this:

UPDATE  acs
SET     [TotalSalesAmount] = [fis].[TotalSalesAmount]
FROM    [dbo].[AnnualCategorySales]     AS acs
JOIN    (
        SELECT  [EnglishProductCategoryName]
        ,       [CalendarYear]
        ,       SUM([SalesAmount])              AS [TotalSalesAmount]
        FROM    [dbo].[FactInternetSales]       AS s
        JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
        JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
        JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
        JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
        WHERE   [CalendarYear] = 2004
        GROUP BY
                [EnglishProductCategoryName]
        ,       [CalendarYear]
        ) AS fis
ON  [acs].[EnglishProductCategoryName]  = [fis].[EnglishProductCategoryName]
AND [acs].[CalendarYear]                = [fis].[CalendarYear]
;

Sachant que Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) ne prend pas en charge les jointures ANSI dans la clause FROM d’une instruction UPDATE, vous ne pouvez pas utiliser ce code SQL Server sans le modifier légèrement.Since Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) does not support ANSI joins in the FROM clause of an UPDATE statement, you cannot use this SQL Server code over without changing it slightly.

Vous pouvez remplacer ce code en combinant CTAS et une jointure implicite :You can use a combination of a CTAS and an implicit join to replace this code:

-- Create an interim table
CREATE TABLE CTAS_acs
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT  ISNULL(CAST([EnglishProductCategoryName] AS NVARCHAR(50)),0)    AS [EnglishProductCategoryName]
,       ISNULL(CAST([CalendarYear] AS SMALLINT),0)                      AS [CalendarYear]
,       ISNULL(CAST(SUM([SalesAmount]) AS MONEY),0)                     AS [TotalSalesAmount]
FROM    [dbo].[FactInternetSales]       AS s
JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
WHERE   [CalendarYear] = 2004
GROUP BY
        [EnglishProductCategoryName]
,       [CalendarYear]
;

-- Use an implicit join to perform the update
UPDATE  AnnualCategorySales
SET     AnnualCategorySales.TotalSalesAmount = CTAS_ACS.TotalSalesAmount
FROM    CTAS_acs
WHERE   CTAS_acs.[EnglishProductCategoryName] = AnnualCategorySales.[EnglishProductCategoryName]
AND     CTAS_acs.[CalendarYear]               = AnnualCategorySales.[CalendarYear]
;

--Drop the interim table
DROP TABLE CTAS_acs
;

K.K. Utiliser CTAS pour spécifier les données à conserver au lieu d’utiliser des jointures ANSI dans la clause FROM d’une instruction DELETEUse CTAS to specify which data to keep instead of using ANSI joins in the FROM clause of a DELETE statement

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

Parfois, la meilleure approche pour supprimer des données est d’utiliser CTAS.Sometimes the best approach for deleting data is to use CTAS. Au lieu de supprimer les données, sélectionnez simplement les données que vous voulez conserver.Rather than deleting the data simply select the data you want to keep. Cela est particulièrement vrai pour les instructions DELETE qui utilisent la syntaxe de jointure ANSI, car Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) ne prend pas en charge les jointures ANSI dans la clause FROM d’une instruction DELETE.This especially true for DELETE statements that use ansi joining syntax since Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) does not support ANSI joins in the FROM clause of a DELETE statement.

Voici un exemple d’instruction DELETE convertie :An example of a converted DELETE statement is available below:

CREATE TABLE dbo.DimProduct_upsert
WITH
(   Distribution=HASH(ProductKey)
,   CLUSTERED INDEX (ProductKey)
)
AS -- Select Data you wish to keep
SELECT     p.ProductKey
,          p.EnglishProductName
,          p.Color
FROM       dbo.DimProduct p
RIGHT JOIN dbo.stg_DimProduct s
ON         p.ProductKey = s.ProductKey
;

RENAME OBJECT dbo.DimProduct        TO DimProduct_old;
RENAME OBJECT dbo.DimProduct_upsert TO DimProduct;

L.L. Utiliser CTAS pour simplifier les instructions mergeUse CTAS to simplify merge statements

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

Les instructions merge peuvent être remplacées, du moins en partie, à l’aide de CTAS.Merge statements can be replaced, at least in part, by using CTAS. Vous pouvez regrouper INSERT et UPDATE dans une même instruction.You can consolidate the INSERT and the UPDATE into a single statement. Les enregistrements supprimés doivent être fermés dans une deuxième instruction.Any deleted records would need to be closed off in a second statement.

Voici un exemple avec UPSERT :An example of an UPSERT is available below:

CREATE TABLE dbo.[DimProduct_upsert]
WITH
(   DISTRIBUTION = HASH([ProductKey])
,   CLUSTERED INDEX ([ProductKey])
)
AS
-- New rows and new versions of rows
SELECT      s.[ProductKey]
,           s.[EnglishProductName]
,           s.[Color]
FROM      dbo.[stg_DimProduct] AS s
UNION ALL  
-- Keep rows that are not being touched
SELECT      p.[ProductKey]
,           p.[EnglishProductName]
,           p.[Color]
FROM      dbo.[DimProduct] AS p
WHERE NOT EXISTS
(   SELECT  *
    FROM    [dbo].[stg_DimProduct] s
    WHERE   s.[ProductKey] = p.[ProductKey]
)
;

RENAME OBJECT dbo.[DimProduct]          TO [DimProduct_old];
RENAME OBJECT dbo.[DimProduct_upsert]  TO [DimProduct];

M.M. déclarer explicitement le type de données et la possibilité de valeur NULL de la sortieExplicitly state data type and nullability of output

S’applique à : Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) et Parallel Data WarehouseParallel Data WarehouseApplies to: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data WarehouseParallel Data Warehouse

Au moment de migrer du code SQL Server vers Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse), il se peut que vous rencontriez un modèle de codage de ce type :When migrating SQL Server code to Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse), you might find you run across this type of coding pattern:

DECLARE @d DECIMAL(7,2) = 85.455
,       @f FLOAT(24)    = 85.455

CREATE TABLE result
(result DECIMAL(7,2) NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN)

INSERT INTO result
SELECT @d*@f
;

Vous pourriez penser instinctivement que ce code doit être migré vers CTAS, et vous auriez raison.Instinctively you might think you should migrate this code to a CTAS and you would be correct. Or, il y a un problème caché.However, there is a hidden issue here.

Le code suivant NE produit PAS le même résultat :The following code does NOT yield the same result:

DECLARE @d DECIMAL(7,2) = 85.455
,       @f FLOAT(24)    = 85.455
;

CREATE TABLE ctas_r
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT @d*@f as result
;

Notez que la colonne « result » reprend le type de données et la possibilité de valeur NULL de l’expression.Notice that the column "result" carries forward the data type and nullability values of the expression. Cela peut occasionner de légers écarts dans les valeurs si vous ne faites pas attention.This can lead to subtle variances in values if you aren't careful.

Faites un essai avec l’exemple suivant :Try the following as an example:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Les valeurs de résultats enregistrées sont différentes.The value stored for result is different. Comme la valeur persistante dans la colonne « result » est utilisée dans d’autres expressions, l’erreur devient plus significative.As the persisted value in the result column is used in other expressions the error becomes even more significant.

Résultats de CREATE TABLE AS SELECT

Cela est particulièrement important pour les migrations de données.This is particularly important for data migrations. Même si la deuxième requête est sans doute plus précise, il y a un problème.Even though the second query is arguably more accurate there is a problem. Les données sont différentes par rapport au système source, ce qui soulève la question de l’intégrité de la migration.The data would be different compared to the source system and that leads to questions of integrity in the migration. Il s’agit de l’un des rares cas où la « mauvaise » réponse est en fait la bonne réponse !This is one of those rare cases where the "wrong" answer is actually the right one!

Cette différence entre les deux résultats est liée à la conversion de type (transtypage) implicite.The reason we see this disparity between the two results is down to implicit type casting. Dans le premier exemple, la table spécifie la définition de colonne.In the first example the table defines the column definition. Au moment où la ligne est insérée, une conversion de type implicite se produit.When the row is inserted an implicit type conversion occurs. Dans le deuxième exemple, il n’y a pas de conversion de type implicite, car l’expression définit le type de données de la colonne.In the second example there is no implicit type conversion as the expression defines data type of the column. Il est aussi à noter que la colonne dans le deuxième exemple a été définie en tant que colonne pouvant accepter les valeurs NULL, ce qui n’est pas le cas dans le premier exemple.Notice also that the column in the second example has been defined as a NULLable column whereas in the first example it has not. Quand la table a été créée dans le premier exemple, la possibilité de valeur NULL dans la colonne était définie explicitement.When the table was created in the first example column nullability was explicitly defined. Dans le deuxième exemple, elle a juste été laissée dans l’expression ce qui, par défaut, donne une définition NULL.In the second example it was just left to the expression and by default this would result in a NULL definition.

Pour éviter ce type de problème, vous devez définir explicitement la conversion de type et la possibilité de valeur NULL dans la partie SELECT de l’instruction CTAS.To resolve these issues you must explicitly set the type conversion and nullability in the SELECT portion of the CTAS statement. Vous ne pouvez pas définir ces propriétés dans la partie « create table ».You cannot set these properties in the create table part.

L’exemple ci-dessous montre comment corriger le code :The example below demonstrates how to fix the code:

DECLARE @d DECIMAL(7,2) = 85.455
,       @f FLOAT(24)    = 85.455

CREATE TABLE ctas_r
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT ISNULL(CAST(@d*@f AS DECIMAL(7,2)),0) as result

Notez les points suivants :Note the following:

  • CAST ou CONVERT aurait pu être utiliséCAST or CONVERT could have been used
  • ISNULL est utilisé pour forcer la possibilité de valeur NULL et non COALESCEISNULL is used to force NULLability not COALESCE
  • ISNULL est la fonction la plus à l’extérieurISNULL is the outermost function
  • La deuxième partie de ISNULL est une constante, c’est-à-dire 0The second part of the ISNULL is a constant i.e. 0

Notes

Pour que la possibilité de valeur NULL soit correctement définie, il est indispensable d’utiliser ISNULL et non COALESCE.For the nullability to be correctly set it is vital to use ISNULL and not COALESCE. COALESCE n’est pas une fonction déterministe. De ce fait, le résultat de l’expression peut toujours prendre la valeur NULL.COALESCE is not a deterministic function and so the result of the expression will always be NULLable. La fonction ISNULL est différente.ISNULL is different. Elle est déterministe.It is deterministic. Par conséquent, quand la deuxième partie de la fonction ISNULL est une constante ou un littéral, la valeur obtenue n’est pas NULL.Therefore when the second part of the ISNULL function is a constant or a literal then the resulting value will be NOT NULL.

Ce conseil n’est pas seulement utile pour assurer l’intégrité de vos calculs.This tip is not just useful for ensuring the integrity of your calculations. Il est aussi important pour le basculement de partition de table.It is also important for table partition switching. Imaginez que vous avez défini cette table :Imagine you have this table defined as your fact:

CREATE TABLE [dbo].[Sales]
(
    [date]      INT     NOT NULL
,   [product]   INT     NOT NULL
,   [store]     INT     NOT NULL
,   [quantity]  INT     NOT NULL
,   [price]     MONEY   NOT NULL
,   [amount]    MONEY   NOT NULL
)
WITH
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
)
;

Or, il s’avère que le champ de valeur est une expression calculée ; il ne fait pas partie des données sources.However, the value field is a calculated expression it is not part of the source data.

Pour créer un jeu de données partitionné, voici ce que vous devez faire :To create your partitioned dataset you might want to do this:

CREATE TABLE [dbo].[Sales_in]
WITH    
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101
                    )
                )
)
AS
SELECT
    [date]    
,   [product]
,   [store]
,   [quantity]
,   [price]   
,   [quantity]*[price]  AS [amount]
FROM [stg].[source]
OPTION (LABEL = 'CTAS : Partition IN table : Create')
;

La requête s’exécuterait parfaitement,The query would run perfectly fine. mais le problème se manifesterait quand vous tenteriez de procéder au basculement de partition.The problem comes when you try to perform the partition switch. Les définitions de table ne correspondent pas.The table definitions do not match. Pour que les définitions de table correspondent, CTAS doit être modifié.To make the table definitions match the CTAS needs to be modified.

CREATE TABLE [dbo].[Sales_in]
WITH    
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101
                    )
                )
)
AS
SELECT
    [date]    
,   [product]
,   [store]
,   [quantity]
,   [price]   
,   ISNULL(CAST([quantity]*[price] AS MONEY),0) AS [amount]
FROM [stg].[source]
OPTION (LABEL = 'CTAS : Partition IN table : Create');

Comme vous pouvez le remarquer, la cohérence des types et le maintien des propriétés de possibilité de valeur NULL au niveau de CTAS constituent une bonne pratique d’ingénierie.You can see therefore that type consistency and maintaining nullability properties on a CTAS is a good engineering best practice. Elle vous permet de préserver l’intégrité de vos calculs et garantit aussi la possibilité d’un basculement de partition.It helps to maintain integrity in your calculations and also ensures that partition switching is possible.

N.N. Créer un index columnstore cluster ordonné avec MAXDOP 1Create an ordered clustered columnstore index with MAXDOP 1

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

Voir aussiSee Also

CREATE EXTERNAL DATA SOURCE (Transact-SQL) CREATE EXTERNAL DATA SOURCE (Transact-SQL)
CREATE EXTERNAL FILE FORMAT (Transact-SQL) CREATE EXTERNAL FILE FORMAT (Transact-SQL)
CREATE EXTERNAL TABLE (Transact-SQL) CREATE EXTERNAL TABLE (Transact-SQL)
CREATE EXTERNAL TABLE AS SELECT (Transact-SQL) CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
CREATE TABLE (Azure Synapse Analytics) DROP TABLE (Transact-SQL) CREATE TABLE (Azure Synapse Analytics) DROP TABLE (Transact-SQL)
DROP EXTERNAL TABLE (Transact-SQL) DROP EXTERNAL TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
ALTER EXTERNAL TABLE (Transact-SQL)ALTER EXTERNAL TABLE (Transact-SQL)