CREATE TABLE AS SELECT (CTAS)

Článek
06/01/2023

Tento článek vysvětluje příkaz T-SQL CREATE TABLE AS SELECT (CTAS) ve vyhrazeném fondu SQL (dříve SQL DW) pro vývoj řešení. Článek obsahuje také příklady kódu.

CREATE TABLE AS SELECT

Příkaz CREATE TABLE AS SELECT (CTAS) je jednou z nejdůležitějších dostupných funkcí T-SQL. CTAS je paralelní operace, která na základě výstupu příkazu SELECT vytvoří novou tabulku. CTAS je nejjednodušší a nejrychlejší způsob, jak vytvořit a vložit data do tabulky pomocí jediného příkazu.

VYBERTE... INTO vs. CTAS

CTAS je více přizpůsobitelná verze select... Příkaz INTO .

Následuje příklad jednoduchého příkazu SELECT... DO:

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

VYBERTE... INTO neumožňuje v rámci operace změnit metodu distribuce ani typ indexu. Vytvoříte [dbo].[FactInternetSales_new] pomocí výchozího typu distribuce ROUND_ROBIN a výchozí struktury tabulky CLUSTERED COLUMNSTORE INDEX.

Pomocí funkce CTAS můžete na druhou stranu určit distribuci dat tabulky i typ struktury tabulky. Převod předchozího příkladu na CTAS:

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

Poznámka

Pokud se pokoušíte změnit pouze index v operaci CTAS a zdrojová tabulka je distribuovaná hodnotou hash, udržujte stejný distribuční sloupec a datový typ. Tím se zabrání přesunu dat mezi distribucemi během operace, což je efektivnější.

Kopírování tabulky pomocí CTAS

Jedním z nejběžnějších použití CTAS je vytvoření kopie tabulky za účelem změny DDL. Řekněme, že jste původně vytvořili tabulku jako ROUND_ROBINa teď ji chcete změnit na tabulku distribuovanou ve sloupci. CTAS je způsob, jak byste změnili distribuční sloupec. Funkci CTAS můžete použít také ke změně dělení, indexování nebo typů sloupců.

Řekněme, že jste tuto tabulku vytvořili zadáním haldy a použitím výchozího ROUND_ROBINtypu distribuce .

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

Teď chcete vytvořit novou kopii této tabulky s Clustered Columnstore Index, abyste mohli využívat výkon clusterovaných tabulek Columnstore. Tuto tabulku ProductKeychcete také distribuovat v , protože očekáváte spojení v tomto sloupci a chcete se vyhnout přesunu dat během spojení na ProductKey. Nakonec chcete také přidat dělení na OrderDateKey, abyste mohli rychle odstranit stará data vyřazením starých oddílů. Tady je příkaz CTAS, který zkopíruje starou tabulku do nové tabulky.

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;

Nakonec můžete tabulky přejmenovat, abyste v nové tabulce prohodili a starou tabulku vyhodili.

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

DROP TABLE FactInternetSales_old;

Explicitní stavový datový typ a dostupnost výstupu s hodnotou null

Při migraci kódu můžete zjistit, že používáte tento typ kódu:

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;

Možná si myslíte, že byste tento kód měli migrovat do CTAS, a měli byste pravdu. Je tu ale skrytý problém.

Následující kód nedává stejný výsledek:

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;

Všimněte si, že sloupec result (výsledek) přenese hodnoty datového typu a hodnoty nullability výrazu. Přenášení datového typu dopředu může vést k drobným odchylkám hodnot, pokud nejste opatrní.

Vyzkoušejte tento příklad:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

Hodnota uložená pro výsledek se liší. Vzhledem k tomu, že se trvalá hodnota ve sloupci výsledků používá v jiných výrazech, je chyba ještě významnější.

Snímek obrazovky s výsledky CTAS

To je důležité pro migrace dat. I když je druhý dotaz pravděpodobně přesnější, došlo k problému. Data by se v porovnání se zdrojovým systémem lišila, což vede k otázkám integrity migrace. To je jeden z těch vzácných případů, kdy "špatná" odpověď je ve skutečnosti ta správná!

Příčinou nepoměru mezi těmito dvěma výsledky je implicitní přetypování typu. V prvním příkladu tabulka definuje definici sloupce. Po vložení řádku dojde k implicitní převodu typu. Ve druhém příkladu neexistuje žádný implicitní převod typu, protože výraz definuje datový typ sloupce.

Všimněte si také, že sloupec v druhém příkladu byl definován jako sloupec NULLable, zatímco v prvním příkladu nikoli. Při vytvoření tabulky v prvním příkladu byla explicitně definována hodnota null pro sloupec. Ve druhém příkladu byla ponechána na výrazu a ve výchozím nastavení by výsledkem byla definice NULL.

Chcete-li tyto problémy vyřešit, je nutné explicitně nastavit převod typu a nullability v části SELECT příkazu CTAS. Tyto vlastnosti nelze nastavit v příkazu CREATE TABLE. Následující příklad ukazuje, jak opravit kód:

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

Pamatujte na následující:

Můžete použít funkci CAST nebo CONVERT.
K vynucení NULLability použijte funkci ISNULL, nikoli COALESCE. Projděte si následující poznámku.
FUNKCE ISNULL je vnější funkce.
Druhá část knihovny ISNULL je konstanta 0.

Poznámka

Aby byla hodnota nullability správně nastavená, je důležité použít funkci ISNULL a ne COALESCE. COALESCE není deterministická funkce, takže výsledek výrazu bude vždy NULLable. Hodnota ISNULL se liší. Je to deterministické. Proto pokud je druhá část funkce ISNULL konstantou nebo literálem, výsledná hodnota nebude NULL.

Zajištění integrity výpočtů je také důležité pro přepínání oddílů tabulky. Představte si, že máte tuto tabulku definovanou jako tabulku faktů:

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

Pole amount je ale počítaný výraz. Není součástí zdrojových dat.

K vytvoření dělené datové sady můžete použít následující kód:

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

Dotaz by běžel dokonale dobře. Problém nastane, když se pokusíte přepnout oddíly. Definice tabulek se neshodují. Pokud chcete, aby se definice tabulky shodovaly, upravte CTAS a ISNULL přidejte funkci, která zachová atribut nullability sloupce.

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

Můžete vidět, že konzistence typů a udržování vlastností null na CTAS je technickým osvědčeným postupem. Pomáhá udržovat integritu výpočtů a také zajišťuje, že je možné přepínat oddíly.

CTAS je jedním z nejdůležitějších příkazů v Synapse SQL. Ujistěte se, že mu důkladně rozumíte. Projděte si dokumentaci ke CTAS.

Další kroky

Další tipy pro vývoj najdete v přehledu vývoje.

Share via

CREATE TABLE AS SELECT (CTAS)