CREATE TABLE AS SELECT

Si applica a:Azure Synapse Analytics AnalyticsPlatform System (PDW)

CREATE TABLE AS SELECT (CTAS) è una delle più importanti funzionalità T-SQL disponibili. È un'operazione completamente parallelizzata che crea una nuova tabella basata sull'output di un'istruzione SELECT. CTAS è il modo più semplice e rapido per creare una copia di una tabella.

Ad esempio, usare CTAS per:

• Ricreare una tabella con una colonna di distribuzione hash diversa.
• Ricreare una tabella come replicata.
• Crea un indice columnstore solo per alcune colonne della tabella.
• Eseguire query o importare dati esterni.

Nota

Poiché CTAS fa parte delle funzionalità di creazione di una tabella, questo argomento non ripropone i contenuti dell'argomento CREATE TABLE. Descrive invece le differenze tra le istruzioni CTAS e CREATE TABLE. Per i dettagli su CREATE TABLE, vedere l'istruzione CREATE TABLE (Azure Synapse Analytics).

• Questa sintassi non è supportata da pool SQL serverless in Azure Synapse Analytics.
• CTAS è supportato nel warehouse in Microsoft Fabric.

Convenzioni di sintassi Transact-SQL

Sintassi

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 = HASH ( [distribution_column_name [, ...n]] )
      | 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 
    }

Argomenti

Per informazioni dettagliate, vedere la sezione degli argomenti in CREATE TABLE.

Opzioni colonna

column_name [ ,...n ]
I nomi delle colonne non consentono le opzioni di colonna indicate in CREATE TABLE. In alternativa, è possibile specificare un elenco facoltativo di uno o più nomi di colonna per la nuova tabella. Le colonne della nuova tabella usano i nomi specificati. Quando si specificano nomi di colonna, il numero di colonne nell'elenco di colonne deve corrispondere al numero di colonne nei risultati di SELECT. Se non si specificano nomi di colonna, la nuova tabella di destinazione usa i nomi delle colonne nei risultati dell'istruzione select.

Non è possibile specificare altre opzioni di colonna, ad esempio tipi di dati, regole di confronto o valori Null. Ognuno di questi attributi è derivato dai risultati dell'istruzione SELECT. Tuttavia, è possibile usare l'istruzione SELECT per modificare gli attributi. Per un esempio, vedere le indicazioni sull'uso di CTAS per modificare gli attributi di colonna.

Opzioni di distribuzione della tabella

Per maggiori dettagli e per comprendere come scegliere la colonna di distribuzione migliore, vedere la sezione Opzioni di distribuzione della tabella in CREATE TABLE. Per le raccomandazioni sul tipo di distribuzione da scegliere per una tabella in base all'utilizzo effettivo o alle query di esempio, vedere Advisor distribuzione in Azure Synapse SQL.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | REPLICATE L'istruzione CTAS richiede un'opzione di distribuzione e non ha valori predefiniti. È pertanto differente dall'istruzione CREATE TABLE, che ha valori predefiniti.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Distribuisce le righe in base ai valori hash di un massimo di otto colonne, consentendo una distribuzione più uniforme dei dati della tabella di base, riducendo l'asimmetria dei dati nel tempo e migliorando le prestazioni delle query.

Nota

• Per abilitare la funzionalità, impostare il livello di compatibilità del database su 50 con questo comando. Per altre informazioni sull'impostazione del livello di compatibilità del database, vedere ALTER DATABA edizione Standard SCOPED CONFIGURATION. Ad esempio: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• Per disabilitare la funzionalità di distribuzione a più colonne , eseguire questo comando per modificare il livello di compatibilità del database su AUTO. Ad esempio: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; le tabelle MCD esistenti rimarranno ma diventano illeggibili. Le query sulle tabelle MCD restituiranno l'errore: Related table/view is not readable because it distributes data on multiple columns and multi-column distribution is not supported by this product version or this feature is disabled.
  • Per ottenere nuovamente l'accesso alle tabelle MCD, abilitare di nuovo la funzionalità.
  • Per caricare i dati in una tabella MCD, usare l'istruzione CTAS. Tenere presente che l'origine dati deve corrispondere a tabelle di Synapse SQL.
  • CTAS nelle tabelle di destinazione HEAP MCD non è supportato. Usare invece IN edizione Standard RT edizione Standard LECT come soluzione alternativa per caricare i dati nelle tabelle HEAP MCD.
• L'uso di SSMS per la generazione di uno script per la creazione di tabelle MCD è attualmente supportato oltre la versione 19 di SSMS.

Per maggiori dettagli e per comprendere come scegliere la colonna di distribuzione migliore, vedere la sezione Opzioni di distribuzione della tabella in CREATE TABLE.

Per consigli sulla distribuzione ottimale da usare in base ai carichi di lavoro, vedere Synapse SQL Distribution Advisor (anteprima).

Opzioni di partizione della tabella

L'istruzione CTAS crea una tabella non partizionata per impostazione predefinita, anche se la tabella di origine è partizionata. Per creare una tabella partizionata con l'istruzione CTAS, è necessario specificare l'opzione di partizione.

Per informazioni dettagliate, vedere la sezione Opzioni di partizione della tabella in CREATE TABLE.

Istruzione SELECT

L'istruzione edizione Standard LECT è la differenza fondamentale tra CTAS e CREATE TABLE.

WITHcommon_table_expression

Indica un set di risultati denominato temporaneo, noto come espressione di tabella comune (CTE). Per altre informazioni, vedere WITH common_table_expression (Transact-SQL).

SELECTselect_criteria

Popola la nuova tabella con i risultati di un'istruzione SELECT. select_criteria è il corpo dell'istruzione SELECT che determina i dati da copiare nella nuova tabella. Per informazioni sulle istruzioni SELECT, vedere SELECT (Transact-SQL).

Hint per la query

Gli utenti possono impostare MAXDOP su un valore integer per controllare il grado massimo di parallelismo. Quando MAXDOP è impostato su 1, la query viene eseguita da un singolo thread.

Autorizzazioni

CTAS richiede l'autorizzazione SELECT per tutti gli oggetti a cui si fa riferimento in select_criteria.

Per le autorizzazioni per la creazione di una tabella, vedere Autorizzazioni in CREATE TABLE.

Osservazioni:

Per informazioni dettagliate, vedere Osservazioni generali in CREATE TABLE.

Limitazioni e restrizioni

È possibile creare un indice columnstore cluster ordinato in colonne con qualsiasi tipo di dati supportato in Azure Synapse Analytics, ad eccezione delle colonne stringa.

L'istruzione SET ROWCOUNT (Transact-SQL) non influisce sull'istruzione CTAS. Per ottenere un comportamento simile, usare TOP (Transact-SQL).

Per informazioni dettagliate, vedere Limitazioni e restrizioni in CREATE TABLE.

Comportamento di blocco

Per informazioni dettagliate, vedere Comportamento di blocco in CREATE TABLE.

Prestazioni

Per una tabella hash distribuita, è possibile usare CTAS e scegliere una colonna di distribuzione diversa per ottenere prestazioni migliori di join e aggregazioni. Se si sceglie una colonna di distribuzione diversa non si tratta dell'obiettivo, si otterranno le migliori prestazioni CTAS se si specifica la stessa colonna di distribuzione, perché in questo modo si evita di ridistribuire le righe.

Se si usa CTAS per creare tabelle e prestazioni non è un fattore, è possibile specificare ROUND_ROBIN per evitare di dover decidere una colonna di distribuzione.

Per evitare spostamenti di dati nelle query successive, è possibile specificare REPLICATE, aumentando in questo modo l'archiviazione, per caricare una copia completa della tabella in ogni nodo di calcolo.

Esempi per la copia di una tabella

R. Usare CTAS per copiare una tabella

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

Forse uno degli usi più comuni di CTAS è creare una copia di una tabella in modo che sia possibile modificare l'istruzione DDL. Si supponga ad esempio di disporre di una tabella creata originariamente come ROUND_ROBIN e di voler trasformare tale tabella in una tabella distribuita in una colonna. L'istruzione CTAS consente di modificare la colonna di distribuzione nel modo opportuno. Si può inoltre usare CTAS per modificare il partizionamento, l'indicizzazione o i tipi di colonna.

Si supponga inoltre di aver creato questa tabella specificando HEAP e usando il tipo di distribuzione predefinito ROUND_ROBIN.

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)
)
WITH( 
 HEAP, 
 DISTRIBUTION = ROUND_ROBIN 
);

Ora si vuole creare una nuova copia di questa tabella con un indice columnstore cluster in modo che sia possibile sfruttare le prestazioni delle tabelle columnstore cluster. Si vuole anche distribuire questa tabella in ProductKey perché si prevede un join in questa colonna e si vuole evitare lo spostamento dei dati durante i join in ProductKey. Si supponga infine di voler aggiungere il partizionamento in OrderDateKey per poter eliminare i dati precedenti con più rapidità, rimuovendo le partizioni datate. Ecco l'istruzione CTAS che copia la tabella precedente in una nuova tabella:

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 per passare alla nuova tabella e quindi eliminare quella precedente.

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

DROP TABLE FactInternetSales_old;

Esempi per le opzioni delle colonne

B. Usare CTAS per modificare gli attributi di colonna

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

In questo esempio viene usata un'istruzione CTAS per modificare i tipi dei dati, il supporto dei valori Null e le regole di confronto per diverse colonne della tabella DimCustomer2.

-- 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);

Come passaggio finale, è possibile usare RENAME (Transact-SQL) per rinominare le tabelle. In questo modo DimCustomer2 diventa la nuova tabella.

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

DROP TABLE DimCustomer2_old;

Esempi per la distribuzione della tabella

C. Usare CTAS per modificare il metodo di distribuzione per una tabella

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

Questo semplice esempio illustra come modificare il metodo di distribuzione per una tabella. Evidenzia il modo in cui una tabella hash distribuita viene trasformata in round robin e la tabella round robin viene di nuovo trasformata in tabella hash distribuita. La tabella finale corrisponde alla tabella originale.

Nella maggior parte dei casi, non è necessario modificare una tabella con distribuzione hash in una tabella round robin. Più spesso può essere necessario modificare una tabella round robin in una tabella hash distribuita. Si può ad esempio caricare inizialmente una nuova tabella come round robin e successivamente trasformarla in una tabella hash distribuita per ottenere migliori prestazioni di join.

In questo esempio viene usato il database AdventureWorksDW. Per caricare la versione di Azure Synapse Analytics, vedere Avvio rapido: Creare ed eseguire query su un pool SQL dedicato (in precedenza SQL DW) in Azure Synapse Analytics usando il portale di Azure.

-- 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];

Successivamente, trasformarla di nuovo in tabella hash distribuita.

-- 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. Usare CTAS per convertire una tabella in una tabella replicata

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

Questo esempio è valido per la conversione di una tabella round robin o hash distribuita in una tabella replicata. Questo particolare esempio porta a un livello successivo il metodo di modifica del tipo di distribuzione visto in precedenza. Poiché DimSalesTerritory è una tabella di dimensioni ed è probabilmente più piccola, è possibile scegliere di ricreare la tabella come tabella replicata per evitare spostamenti di dati quando si esegue il join con altre tabelle.

-- 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. Usare CTAS per creare una tabella con meno colonne

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

Nell'esempio seguente viene creata una tabella round robin distribuita denominata myTable (c, ln). La nuova tabella ha solo due colonne. Usa gli alias di colonna nell'istruzione SELECT per i nomi delle colonne.

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

Esempi per gli hint per la query

F. Usare un hint per la query con CREATE TABLE AS SELECT (CTAS)

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

Questa query illustra la sintassi di base per l'uso di un hint di join per la query con l'istruzione CTAS. Dopo l'invio della query, Azure Synapse Analytics applica la strategia di hash join quando genera il piano di query per ogni singola distribuzione. Per altre informazioni sull'hint per la query di hash join, vedere Clausola OPTION (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 );  

Esempi per le tabelle esterne

G. Usare CTAS per importare dati dall'archivio BLOB di Azure

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

Per importare dati da una tabella esterna, usare CREATE TABLE AS edizione Standard LECT per selezionare dalla tabella esterna. La sintassi per selezionare i dati da una tabella esterna e inserirli in Azure Synapse Analytics è la stessa usata per selezionare i dati da una normale tabella.

Nell'esempio seguente viene definita una tabella esterna sui dati in un account Archiviazione BLOB di Azure. Usa quindi CREATE TABLE AS SELECT per selezionare dati dalla tabella esterna. I dati vengono importati da Archiviazione BLOB di Azure file delimitati da testo e i dati vengono archiviati in una nuova tabella di Azure Synapse Analytics.

--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. Usare CTAS per importare dati Hadoop da una tabella esterna

Si applica a: Piattaforma di strumenti analitici (PDW)

Per importare dati da una tabella esterna, usare CREATE TABLE AS SELECT per selezionare i dati dalla tabella esterna. La sintassi per selezionare i dati da una tabella esterna e inserirli nella piattaforma di strumenti analitici (PDW) è la stessa usata per selezionare i dati da una normale tabella.

L'esempio seguente definisce una tabella esterna per un cluster Hadoop. Usa quindi CREATE TABLE AS SELECT per selezionare dati dalla tabella esterna. I dati vengono importati dai file di testo delimitato di Hadoop e archiviati in una nuova tabella della piattaforma di strumenti analitici (PDW).

-- 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  
;   

Esempi di uso di CTAS per sostituire il codice di SQL Server

Usare CTAS come soluzione alternativa se alcune funzionalità non sono supportate. Oltre a consentire l'esecuzione del codice nel data warehouse, la riscrittura del codice esistente per l'uso di CTAS in genere migliora le prestazioni. Questo è il risultato di una progettazione eseguita completamente in parallelo.

Nota

Provare a considerare CTAS come prima soluzione. Se si ritiene che sia possibile risolvere un problema usando CTAS, in genere questo è il modo migliore per affrontarlo, anche se si devono scrivere più dati.

I. Usare CTAS anziché SELECT...INTO

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

Il codice di SQL Server in genere usa SELECT...INTO per popolare una tabella con i risultati di un'istruzione SELECT. Questo è un esempio di istruzione SELECT..INTO di SQL Server.

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

Questa sintassi non è supportata in Azure Synapse Analytics e Parallel Data Warehouse. Questo esempio illustra come riscrivere l'istruzione SELECT...INTO precedente come istruzione CTAS. È possibile scegliere una qualsiasi delle opzioni di distribuzione descritte nella sintassi CTAS. In questo esempio viene usato il metodo di distribuzione ROUND_ROBIN.

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

J. Usare CTAS per semplificare le istruzioni MERGE

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

Le istruzioni MERGE possono essere sostituite, almeno in parte, usando CTAS. È possibile consolidare INSERT e UPDATE in una singola istruzione. Eventuali record eliminati dovranno essere isolati in una seconda istruzione.

Di seguito è riportato un UPSERT esempio di:

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];

K. dichiarare in modo esplicito il tipo di dati e il supporto dei valori Null di output

Si applica a: Azure Synapse Analytics, Piattaforma di strumenti analitici (PDW)

Durante la migrazione del codice di SQL Server in Azure Synapse Analytics, è possibile incontrare il tipo di modello di codifica seguente:

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 può pensare, con ragione, che è necessario eseguire la migrazione del codice in un'istruzione CTAS. Tuttavia, c'è un problema nascosto qui.

Il codice seguente NON produce lo stesso risultato:

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 del risultato riporta i valori relativi a tipo di dati e supporto dei valori Null dell'espressione. Questo può causare lievi variazioni nei valori se non si presta attenzione.

Provare quanto segue a titolo di esempio:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Il valore archiviato per il risultato è diverso. Poiché il valore persistente nella colonna del risultato viene usato in altre espressioni, l'errore diventa ancora più significativo.

Questo aspetto è importante per le migrazioni dei dati. Anche se la seconda query è probabilmente più accurata, esiste un problema. I dati risulterebbero diversi rispetto al sistema di origine e ciò comporta problemi di integrità della migrazione. Questo è uno dei rari casi in cui la risposta "sbagliata" in effetti è quella giusta.

Il motivo per cui si nota questa disparità tra i due risultati è il cast dei tipi eseguito in modo implicito. Nel primo esempio la tabella determina la definizione di colonna. Quando viene inserita la riga si verifica una conversione implicita del tipo. Nel secondo esempio non esiste alcuna conversione implicita del tipo perché l'espressione definisce il tipo di dati della colonna. Si noti anche che la colonna nel secondo esempio è stata definita come colonna NULLable, mentre nel primo esempio non è stata definita. Durante la creazione della tabella del primo esempio il supporto dei valori Null da parte della colonna è stato definito in modo esplicito. Nel secondo esempio, è stato lasciato all'espressione e per impostazione predefinita si ottiene una NULL definizione.

Per risolvere questi problemi, è necessario impostare in modo esplicito la conversione del tipo e il supporto dei valori Null nella parte SELECT dell'istruzione CTAS. Non è possibile impostare queste proprietà nella parte crea tabella.

Questo esempio illustra come correggere il codice:

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

Si noti quanto segue nell'esempio:

• È possibile usare CAST o CONVERT.
• ISNULL viene usato per forzare NULLability non COALESCE.
• ISNULL è la funzione più esterna.
• La seconda parte di ISNULL è una costante, 0.

Nota

Per impostare in modo corretto il supporto dei valori Null, è essenziale usare ISNULL e non COALESCE. COALESCE non è una funzione deterministica e quindi il risultato dell'espressione ammette sempre i valori Null. ISNULL è differente. È deterministica. Quindi, se la seconda parte della funzione ISNULL è una costante o un valore letterale, il valore risultante sarà NOT NULL.

Questo suggerimento non è utile solo per garantire l'integrità dei calcoli. È anche importante per il cambio di partizione della tabella. Si supponga che questa tabella sia definita come 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 valore è un'espressione calcolata che non fa parte dei dati di origine.

Per creare il set di dati partizionato, fare riferimento all'esempio seguente:

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 in modo corretto. Il problema si verifica quando si tenta di eseguire la commutazione delle partizioni. Le definizioni di tabella non corrispondono. Per fare in modo che le definizioni di tabella corrispondano, è necessario modificare l'istruzione CTAS.

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');

È quindi possibile vedere come la coerenza dei tipi e la gestione delle proprietà del supporto dei valori Null in CTAS siano importanti ai fini della progettazione. In questo modo si preserva l'integrità dei calcoli e si garantisce che la commutazione delle partizioni sia possibile.

.L Creare un indice columnstore cluster ordinato con MAXDOP 1

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

Passaggi successivi

• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE EXTERNAL TABLE AS edizione Standard LECT (Transact-SQL)
• CREATE TABLE (Azure Synapse Analytics)
• DROP TABLE (Transact-SQL)
• DROP EXTERNAL TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)
• ALTER EXTERNAL TABLE (Transact-SQL)