CREATE TABLE AS SELECT (CTAS)
L'articolo spiega l'istruzione T-SQL CREATE TABLE AS SELECT (CTAS) nel pool SQL dedicato (in precedenza SQL Data Warehouse) per lo sviluppo di soluzioni. L'articolo include anche esempi di codice.
CREATE TABLE AS SELECT
L'istruzione CREATE TABLE AS SELECT (CTAS) è una delle funzionalità più importanti disponibili in T-SQL. CTAS un'operazione parallela che crea una nuova tabella basata sull'output di un'istruzione SELECT. CTAS è il modo più semplice e rapido per creare e inserire dati in una tabella con un singolo comando.
SELECT…INTO e CTAS
CTAS è una versione più personalizzabile dell'istruzione SELECT…INTO.
Di seguito è riportato un esempio di una semplice istruzione SELECT…INTO:
SELECT *
INTO [dbo].[FactInternetSales_new]
FROM [dbo].[FactInternetSales]
SELECT...INTO non consente di modificare il metodo di distribuzione o il tipo di indice come parte dell'operazione. È possibile creare [dbo].[FactInternetSales_new] usando il tipo di distribuzione predefinito di ROUND_ROBIN e la struttura di tabella predefinita di CLUSTERED COLUMNSTORE INDEX.
Con CTAS, d'altra parte, è possibile specificare sia la distribuzione dei dati delle tabelle, sia il tipo di struttura delle tabelle. Per convertire l'esempio precedente in un'istruzione CTAS:
CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
DISTRIBUTION = ROUND_ROBIN
,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM [dbo].[FactInternetSales];
Nota
Se si intende solo modificare l'indice nell'operazione CTAS e la tabella di origine è con distribuzione hash, mantenere la stessa distribuzione di colonne e tipo di dati. In questo modo si evitano spostamenti dati incrociati di distribuzione durante le operazioni con migliori risultati.
Usare CTAS per copiare una tabella
Forse uno degli usi più comuni di CTAS è creare una copia di una tabella per modificare l'istruzione DDL. Si supponga di aver originariamente creato la tabella come ROUND_ROBIN e ora si vuole modificarla in una tabella distribuita in una colonna. CTAS è la modalità di modifica della colonna di distribuzione. È anche possibile 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 Clustered Columnstore Index in modo che sia possibile sfruttare le prestazioni delle tabelle columnstore cluster. Poiché è previsto l'uso di join per la colonna, potrebbe essere opportuno distribuire la tabella in ProductKey, in modo da evitare spostamenti di dati durante i join in ProductKey. Si supponga infine di voler aggiungere il partizionamento in OrderDateKey per eliminare i dati precedenti con più rapidità, rimuovendo le partizioni datate. Di seguito è riportata l'istruzione CTAS che copia la vecchia tabella 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;
dichiarare in modo esplicito il tipo di dati e il supporto dei valori Null di output
Durante la migrazione del codice, è possibile imbattersi in questo tipo di modello di codifica:
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;
Si potrebbe pensare, con ragione, di eseguire la migrazione di questo codice a CTAS. Questa operazione però nasconde un problema.
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. Lo spostamento in avanti del tipo di dati può causare lievi variazioni nei valori se non si presta attenzione.
Provare a eseguire questo 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 è importante per le migrazioni di dati. Anche se la seconda query probabilmente è più precisa, 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 una disparità tra i due risultati è dovuto al 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 inoltre che, a differenza del primo esempio, la colonna del secondo esempio è stata definita come colonna che ammette valori Null. 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 gestito dall'espressione e per impostazione predefinita si è ottenuta una definizione NULL.
Per risolvere questi problemi è necessario impostare esplicitamente la conversione del tipo e il supporto di valori Null nella parte SELECT dell'istruzione CTAS. Non è possibile impostare queste proprietà in "CREATE TABLE". L'esempio seguente 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
Nota quanto segue:
- È possibile usare CAST o CONVERT.
- Usare ISNULL, non COALESCE, per forzare il supporto dei valori Null. Vedere la nota seguente.
- ISNULL è la funzione più esterna.
- La seconda parte di ISNULL è una costante, 0.
Nota
Per impostare correttamente il supporto di valori Null è fondamentale usare ISNULL e non COALESCE. COALESCE non è una funzione deterministica e pertanto il risultato dell'espressione sarà sempre NULLable. ISNULL è diversa. È deterministica. Perciò, quando la seconda parte della funzione ISNULL è una costante o un valore letterale, il valore risultante sarà NOT NULL.
Garantire l'integrità dei calcoli è importante anche per il cambio di partizione della tabella. Si supponga che questa tabella sia definita come tabella dei fatti:
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 dell'importo è un'espressione calcolata. Non fa parte dei dati di origine.
Per creare il set di dati partizionato, è possibile usare il codice 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 della tabella corrispondano, modificare CTAS per aggiungere una funzione ISNULL per mantenere l'attributo di supporto dei valori Null della colonna.
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');
È 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.
CTAS è una delle istruzioni più importanti di Synapse SQL. Assicurarsi di averla compresa completamente. Vedere la documentazione CTAS.
Passaggi successivi
Per altri suggerimenti relativi allo sviluppo, vedere la panoramica sullo sviluppo.