Create Table As Select (CTAS) in SQL Data WarehouseCreate Table As Select (CTAS) in SQL Data Warehouse

Create Table As Select o CTAS è una delle funzionalità più importanti disponibili in T-SQL.Create table as select or CTAS is one of the most important T-SQL features available. È un'operazione completamente parallelizzata che crea una nuova tabella basata sull'output di un'istruzione SELECT.It is a fully parallelized operation that creates a new table based on the output of a SELECT statement. CTAS è il modo più semplice e veloce di creare la copia di una tabella.CTAS is the simplest and fastest way to create a copy of a table. Questo documento offre esempi e procedure consigliate per CTAS.This document provides both examples and best practices for CTAS.

SELECT..INTO e CTASSELECT..INTO vs. CTAS

È possibile considerare CTAS come una versione più potente di SELECT..INTO.You can consider CTAS as a super-charged version of SELECT..INTO.

Di seguito è riportato un esempio di un'istruzione semplice SELECT..INTO:Below is an example of a simple SELECT..INTO statement:

SELECT *
INTO    [dbo].[FactInternetSales_new]
FROM    [dbo].[FactInternetSales]

Nell'esempio precedente [dbo].[FactInternetSales_new] viene creata come una tabella ROUND_ROBIN distribuita con un'istruzione CLUSTERED COLUMNSTORE INDEX in quanto questi sono i valori predefiniti delle tabelle in SQL Data Warehouse di Azure.In the example above [dbo].[FactInternetSales_new] would be created as ROUND_ROBIN distributed table with a CLUSTERED COLUMNSTORE INDEX on it as these are the table defaults in Azure SQL Data Warehouse.

Tuttavia SELECT..INTO non consente di modificare il metodo di distribuzione o il tipo di indice come parte dell'operazione.SELECT..INTO however does not allow you to change either the distribution method or the index type as part of the operation. Qui è dove si posiziona CTAS.This is where CTAS comes in.

Convertire l'istruzione precedente in CTAS è semplice:To convert the above to CTAS is quite straight-forward:

CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
    DISTRIBUTION = ROUND_ROBIN
,   CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
;

Con CTAS è possibile modificare sia la distribuzione dei dati che il tipo della tabella.With CTAS you are able to change both the distribution of the table data as well as the table type.

Nota

Se si intende solo modificare l'indice nell'operazione CTAS e la tabella di origine è con distribuzione hash, è possibile che l'operazione CTAS possa essere eseguita in modo ottimale se si mantiene la stessa distribuzione di colonne e tipo di dati.If you are only trying to change the index in your CTAS operation and the source table is hash distributed then your CTAS operation will perform best if you maintain the same distribution column and data type. In questo modo si eviteranno spostamenti incrociati di distribuzione di dati durante le operazioni con migliori risultati.This will avoid cross distribution data movement during the operation which is more efficient.

Uso di CTAS per copiare una tabellaUsing CTAS to copy a table

Probabilmente uno degli usi più comuni di CTAS consiste nel creare una copia di una tabella in modo da poter modificare il DDL.Perhaps one of the most common uses of CTAS is creating a copy of a table so that you can change the DDL. Se ad esempio la tabella è stata originariamente creata come ROUND_ROBIN e ora si vuole modificarla in una tabella distribuita su una colonna, CTAS rappresenta il metodo di modifica della colonna di distribuzione.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 anche per modificare il partizionamento, l'indicizzazione o i tipi di colonna.CTAS can also be used to change partitioning, indexing, or column types.

Si supponga che questa tabella sia stata creata usando il tipo di distribuzione predefinito di ROUND_ROBIN distribuito, in quanto non è stata specificata alcuna colonna di distribuzione in 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)
);

Ora si desidera creare una nuova copia di questa tabella con un indice cluster columnstore, in modo da poter sfruttare le prestazioni delle tabelle 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. Si desidera anche distribuire la tabella in ProductKey, poiché si prevedono join per la colonna e si desidera evitare lo spostamento dei dati durante i join in ProductKey.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. Infine, si desidera aggiungere il partizionamento in OrderDateKey in modo da poter eliminare rapidamente i vecchi dati eliminando le vecchie partizioni.Lastly you also want to add partitioning on OrderDateKey so that you can quickly delete old data by dropping old partitions. Di seguito è riportata l'istruzione CTAS per copiare la vecchia tabella in una nuova tabella.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;

È infine possibile rinominare le tabelle in modo da scambiare la nuova tabella ed eliminare quella precedente.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;

Nota

SQL Data Warehouse di Azure non supporta ancora le statistiche di creazione automatica o aggiornamento automatico.Azure SQL Data Warehouse does not yet support auto create or auto update statistics. Per ottenere le migliori prestazioni dalle query, è importante creare statistiche per tutte le colonne di tutte le tabelle dopo il primo caricamento o dopo eventuali modifiche sostanziali dei dati.In order to get the best performance from your queries, it's important that statistics be created on all columns of all tables after the first load or any substantial changes occur in the data. Per una spiegazione dettagliata delle statistiche, vedere l'argomento Statistiche nel gruppo di argomenti sullo sviluppo.For a detailed explanation of statistics, see the Statistics topic in the Develop group of topics.

Uso di CTAS per ovviare a funzionalità non supportateUsing CTAS to work around unsupported features

CTAS per ovviare a problemi derivanti da alcune funzionalità non supportate elencate di seguito.CTAS can also be used to work around a number of the unsupported features listed below. Questo può spesso rivelarsi una situazione favorevole in quando non solo il codice sarà conforme, ma l’esecuzione sarà più veloce in SQL Data Warehouse.This can often prove to be a win/win situation as not only will your code be compliant but it will often execute faster on SQL Data Warehouse. Si tratta del risultato della progettazione completamente parallelizzata.This is as a result of its fully parallelized design. Gli scenari che è possibile trattare con CTAS includono:Scenarios that can be worked around with CTAS include:

  • ANSI JOINS su UPDATEANSI JOINS on UPDATEs
  • ANSI JOINs su DELETEANSI JOINs on DELETEs
  • Istruzione MERGEMERGE statement

Nota

Provare a considerare "prima CTAS".Try to think "CTAS first". Se si ritiene che sia possibile risolvere un problema con CTAS , questo approccio in genere si rivela la scelta migliore, anche se è necessario scrivere più dati.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.

Sostituzione di join ANSI per le istruzioni updateANSI join replacement for update statements

È possibile che si dispone di un aggiornamento complesso che unisce in join più di due tabelle utilizzando la sintassi di join ANSI per eseguire un’istruzione UPDATE o DELETE.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.

Si immagini di dover aggiornare questa tabella: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 query originale potrebbe essere simile alla seguente: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]
;

Poiché SQL Data Warehouse non supporta i join ANSI nella clausola FROM di un'istruzione UPDATE, non è possibile copiare questo codice senza modificarlo leggermente.Since SQL Data Warehouse does not support ANSI joins in the FROM clause of an UPDATE statement, you cannot copy this code over without changing it slightly.

È possibile combinare CTAS con un join implicito per sostituire questo codice: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
;

Sostituzione di join ANSI per le istruzioni deleteANSI join replacement for delete statements

Talvolta l'uso di CTASsi rivela l'approccio migliore per l'eliminazione dei dati.Sometimes the best approach for deleting data is to use CTAS. Invece di eliminare i dati, si selezionano semplicemente i dati da mantenere.Rather than deleting the data simply select the data you want to keep. Ciò vale soprattutto per le istruzioni DELETE che usano la sintassi di join ANSI, in quanto SQL Data Warehouse non supporta i join ANSI nella clausola FROM di un'istruzione DELETE.This especially true for DELETE statements that use ansi joining syntax since SQL Data Warehouse does not support ANSI joins in the FROM clause of a DELETE statement.

Un esempio di istruzione DELETE convertita è disponibile di seguito: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;

Sostituzione delle istruzioni mergeReplace merge statements

Le istruzioni merge possono essere sostituite, almeno in parte, usando CTAS.Merge statements can be replaced, at least in part, by using CTAS. È possibile consolidare INSERT e UPDATE in un'unica istruzione.You can consolidate the INSERT and the UPDATE into a single statement. Tutti i record eliminati dovevano essere chiusi in una seconda istruzione.Any deleted records would need to be closed off in a second statement.

Un esempio di UPSERT è riportato di seguito: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.[DimpProduct_upsert]  TO [DimProduct];

Raccomandazione CTAS: dichiarare in modo esplicito il tipo di dati e il supporto di valori null di outputCTAS recommendation: Explicitly state data type and nullability of output

Durante la migrazione del codice, è possibile imbattersi in questo tipo di modello di codifica:When migrating code 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
;

Istintivamente si potrebbe pensare che è necessario eseguire la migrazione di questo codice a CTAS e sarebbe un’operazione corretta.Instinctively you might think you should migrate this code to a CTAS and you would be correct. Tuttavia, esiste un problema nascosto.However, there is a hidden issue here.

Il codice seguente NON produce lo stesso risultato: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
;

Si noti che la colonna "result" porta avanti i valori del tipo di dati e del supporto di valori null dell'espressione.Notice that the column "result" carries forward the data type and nullability values of the expression. Ciò può causare lievi variazioni nei valori, se non si presta attenzione.This can lead to subtle variances in values if you aren't careful.

Provare a eseguire il seguente esempio:Try the following as an example:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Il valore archiviato per il risultato è diverso.The value stored for result is different. Poiché il valore persistente della colonna result viene utilizzato in altre espressioni, l'errore diventa ancora più significativo.As the persisted value in the result column is used in other expressions the error becomes even more significant.

Ciò è particolarmente importante per le migrazioni di dati.This is particularly important for data migrations. Anche se la seconda query è senza dubbio più accurata, esiste un problema.Even though the second query is arguably more accurate there is a problem. I dati sono diversi rispetto al sistema di origine e ciò comporta problemi di integrità della migrazione.The data would be different compared to the source system and that leads to questions of integrity in the migration. Questo è uno dei rari casi in cui la risposta "errata" è effettivamente quella corretta.This is one of those rare cases where the "wrong" answer is actually the right one!

Il motivo per cui esiste questa differenza tra i due risultati è costituito dal cast implicito del tipo.The reason we see this disparity between the two results is down to implicit type casting. Nel primo esempio la tabella specifica la definizione di colonna.In the first example the table defines the column definition. Quando la riga viene inserita si verifica una conversione implicita del tipo.When the row is inserted an implicit type conversion occurs. Nel secondo esempio non esiste alcuna conversione implicita del tipo poiché l'espressione definisce il tipo di dati della colonna.In the second example there is no implicit type conversion as the expression defines data type of the column. Si noti inoltre che la colonna nel secondo esempio è stata definita come una colonna che ammette valori null, mentre nel primo esempio non lo è.Notice also that the column in the second example has been defined as a NULLable column whereas in the first example it has not. Quando la tabella è stata creata nel primo esempio, il supporto di valori null della colonna è stato definito in modo esplicito.When the table was created in the first example column nullability was explicitly defined. Nel secondo esempio, è stato affidato all'espressione che per impostazione predefinita comporterebbe una definizione NULL.In the second example it was just left to the expression and by default this would result in a NULL definition.

Per risolvere questi problemi è necessario impostare esplicitamente la conversione del tipo e il supporto di valori Null nella parte SELECT dell'istruzione CTAS.To resolve these issues you must explicitly set the type conversion and nullability in the SELECT portion of the CTAS statement. Non è possibile impostare queste proprietà nella parte relativa alla creazione della tabella.You cannot set these properties in the create table part.

Il seguente esempio illustra come correggere il codice: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

Tenere presente quanto segue:Note the following:

  • Era possibile usare CAST o CONVERTCAST or CONVERT could have been used
  • ISNULL viene usato per forzare NULLability e non COALESCEISNULL is used to force NULLability not COALESCE
  • ISNULL è la funzione più esternaISNULL is the outermost function
  • La seconda parte di ISNULL è una costante, ad esempio 0The second part of the ISNULL is a constant i.e. 0

Nota

Per impostare correttamente il supporto di valori Null è fondamentale usare ISNULL e non COALESCE.For the nullability to be correctly set it is vital to use ISNULL and not COALESCE. COALESCE non è una funzione deterministica e pertanto il risultato dell'espressione sarà sempre NULLable.COALESCE is not a deterministic function and so the result of the expression will always be NULLable. ISNULL è diverso.ISNULL is different. È deterministico.It is deterministic. Pertanto quando la seconda parte della funzione ISNULL è una costante o un valore letterale, il valore risultante sarà NOT NULL.Therefore when the second part of the ISNULL function is a constant or a literal then the resulting value will be NOT NULL.

Questo suggerimento non è utile solo per garantire l'integrità dei calcoli.This tip is not just useful for ensuring the integrity of your calculations. È importante anche per il cambio di partizione della tabella.It is also important for table partition switching. Si supponga di disporre di questa tabella definita come dato di fatto: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
                    )
                )
)
;

Tuttavia, il campo del valore è un'espressione calcolata che non fa parte dei dati di origine.However, the value field is a calculated expression it is not part of the source data.

Per creare il set di dati partizionati è possibile eseguire questa operazione: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 query verrebbe eseguita correttamente.The query would run perfectly fine. Il problema sorge quando si tenta di eseguire il cambio di partizione.The problem comes when you try to perform the partition switch. Le definizioni di tabella non corrispondono.The table definitions do not match. Per far corrispondere le definizioni di tabella è necessario modificare CTAS.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');

Pertanto è possibile vedere che la coerenza del tipo e le proprietà di gestione del supporto di valori null in CTAS è un’ottima procedura tecnica consigliata.You can see therefore that type consistency and maintaining nullability properties on a CTAS is a good engineering best practice. Consente di mantenere l'integrità dei calcoli e assicura inoltre che il cambio di partizione sia possibile.It helps to maintain integrity in your calculations and also ensures that partition switching is possible.

Fare riferimento a MSDN per ulteriori informazioni sull'utilizzo di CTAS.Please refer to MSDN for more information on using CTAS. È una delle istruzioni più importanti di SQL Data Warehouse di Azure.It is one of the most important statements in Azure SQL Data Warehouse. Assicurarsi di averla compresa completamente.Make sure you thoroughly understand it.

Passaggi successiviNext steps

Per altri suggerimenti sullo sviluppo, vedere la panoramica dello sviluppo.For more development tips, see development overview.