CREATE TABLE AS SELECT (CTAS)

Artikel
06/14/2024

In dit artikel wordt de T-SQL-instructie CREATE TABLE AS SELECT (CTAS) in een toegewezen SQL-pool (voorheen SQL DW) uitgelegd voor het ontwikkelen van oplossingen. Het artikel bevat ook codevoorbeelden.

CREATE TABLE AS SELECT

De instructie CREATE TABLE AS SELECT (CTAS) is een van de belangrijkste T-SQL-functies die beschikbaar zijn. CTAS is een parallelle bewerking waarmee een nieuwe tabel wordt gemaakt op basis van de uitvoer van een SELECT-instructie. CTAS is de eenvoudigste en snelste manier om met één opdracht gegevens in een tabel te maken en in te voegen.

SELECTEREN... INTO versus CTAS

CTAS is een meer aanpasbare versie van de SELECT... INTO-instructie .

Hier volgt een voorbeeld van een eenvoudige SELECT... IN:

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

SELECTEREN... Met INTO kunt u de distributiemethode of het indextype niet wijzigen als onderdeel van de bewerking. U maakt [dbo].[FactInternetSales_new] met behulp van het standaarddistributietype van ROUND_ROBIN en de standaardtabelstructuur van GECLUSTERDE COLUMNSTORE INDEX.

Met CTAS kunt u daarentegen zowel de verdeling van de tabelgegevens als het tabelstructuurtype opgeven. Het vorige voorbeeld converteren naar CTAS:

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

Notitie

Als u alleen de index in de CTAS-bewerking probeert te wijzigen en de brontabel hash wordt gedistribueerd, moet u hetzelfde distributiekolom en gegevenstype onderhouden. Dit voorkomt crossdistributiegegevensverplaatsing tijdens de bewerking, wat efficiënter is.

CTAS gebruiken om een tabel te kopiëren

Misschien is een van de meest voorkomende toepassingen van CTAS het maken van een kopie van een tabel om de DDL te wijzigen. Stel dat u de tabel oorspronkelijk hebt gemaakt als ROUND_ROBIN, en deze nu wilt wijzigen in een tabel die is gedistribueerd op een kolom. CTAS is hoe u de distributiekolom zou wijzigen. U kunt ook CTAS gebruiken om partitionering, indexering of kolomtypen te wijzigen.

Stel dat u deze tabel hebt gemaakt door HEAP op te geven en het standaarddistributietype van 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 
);

Nu wilt u een nieuwe kopie van deze tabel maken, met een Clustered Columnstore Index, zodat u kunt profiteren van de prestaties van geclusterde Columnstore-tabellen. U wilt deze tabel ook distribueren, ProductKeyomdat u op joins in deze kolom verwacht en gegevensverplaatsing wilt voorkomen tijdens joins op ProductKey. Ten slotte wilt u ook partitionering OrderDateKeytoevoegen, zodat u snel oude gegevens kunt verwijderen door oude partities te verwijderen. Hier volgt de CTAS-instructie, waarmee uw oude tabel naar een nieuwe tabel wordt gekopieerd.

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;

Ten slotte kunt u de naam van uw tabellen wijzigen, de nieuwe tabel wisselen en vervolgens de oude tabel verwijderen.

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

DROP TABLE FactInternetSales_old;

Gegevenstype expliciet vermelden en null-uitvoerbaarheid

Wanneer u code migreert, kunt u dit type coderingspatroon tegenkomen:

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;

U denkt misschien dat u deze code naar CTAS moet migreren en dat u juist zou zijn. Er is echter hier een verborgen probleem.

De volgende code levert niet hetzelfde resultaat op:

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;

U ziet dat de kolom 'result' het gegevenstype en de null-waarden van de expressie doorstuurt. Het doorsturen van het gegevenstype kan leiden tot subtiele afwijkingen in waarden als u niet voorzichtig bent.

Probeer dit voorbeeld:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

De waarde die voor het resultaat is opgeslagen, is anders. Omdat de persistente waarde in de resultaatkolom wordt gebruikt in andere expressies, wordt de fout nog belangrijker.

Schermopname van CTAS-resultaten

Dit is belangrijk voor gegevensmigraties. Hoewel de tweede query waarschijnlijk nauwkeuriger is, is er een probleem. De gegevens zouden anders zijn dan het bronsysteem en dat leidt tot vragen over integriteit in de migratie. Dit is een van die zeldzame gevallen waarbij het 'verkeerde' antwoord eigenlijk het juiste is!

De reden waarom we een verschil tussen de twee resultaten zien, is het gevolg van impliciete typecasting. In het eerste voorbeeld definieert de tabel de kolomdefinitie. Wanneer de rij wordt ingevoegd, treedt er een impliciete typeconversie op. In het tweede voorbeeld is er geen impliciete typeconversie omdat de expressie het gegevenstype van de kolom definieert.

U ziet ook dat de kolom in het tweede voorbeeld is gedefinieerd als een nullbare kolom, terwijl deze in het eerste voorbeeld niet is gedefinieerd. Toen de tabel in het eerste voorbeeld werd gemaakt, is de kolom null-waarde expliciet gedefinieerd. In het tweede voorbeeld werd deze overgelaten aan de expressie en zou standaard resulteren in een NULL-definitie.

Als u deze problemen wilt oplossen, moet u expliciet de typeconversie en null-waarde instellen in het SELECT-gedeelte van de CTAS-instructie. U kunt deze eigenschappen niet instellen in CREATE TABLE. In het volgende voorbeeld ziet u hoe u de code kunt herstellen:

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

Let op het volgende:

U kunt CAST of CONVERT gebruiken.
Gebruik ISNULL, niet COALESCE, om NULLability af te dwingen. Zie de volgende opmerking.
ISNULL is de buitenste functie.
Het tweede deel van de ISNULL is een constante, 0.

Notitie

Om de null-waarde correct in te stellen, is het essentieel om ISNULL en niet COALESCE te gebruiken. COALESCE is geen deterministische functie en daarom is het resultaat van de expressie altijd NULLable. ISNULL is anders. Het is deterministisch. Dus wanneer het tweede deel van de functie ISNULL een constante of een letterlijke waarde is, is de resulterende waarde NIET NULL.

Het waarborgen van de integriteit van uw berekeningen is ook belangrijk voor het schakelen tussen tabelpartities. Stel dat u deze tabel hebt gedefinieerd als een feitentabel:

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

Het veld Hoeveelheid is echter een berekende expressie. Het maakt geen deel uit van de brongegevens.

Als u uw gepartitioneerde gegevensset wilt maken, wilt u mogelijk de volgende code gebruiken:

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

De query zou perfect worden uitgevoerd. Het probleem treedt op wanneer u de partitieswitch probeert uit te voeren. De tabeldefinities komen niet overeen. Als u de tabeldefinities wilt laten overeenkomen, wijzigt u de CTAS om een ISNULL functie toe te voegen om het kenmerk nullability van de kolom te behouden.

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

U kunt zien dat typeconsistentie en het onderhouden van eigenschappen van null-baarheid op een CTAS een best practice voor engineering is. Het helpt om de integriteit in uw berekeningen te behouden en zorgt er ook voor dat partitiewisseling mogelijk is.

CTAS is een van de belangrijkste instructies in Synapse SQL. Zorg ervoor dat u het grondig begrijpt. Raadpleeg de CTAS-documentatie.

Volgende stappen

Zie het overzicht van de ontwikkeling voor meer tips voor ontwikkeling.

Share via

CREATE TABLE AS SELECT (CTAS)