CREATE TABLE AS SELECT (CTAS)

Článek
06/14/2024

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á vytvoří novou tabulku na základě výstupu příkazu SELECT. CTAS je nejjednodušší a nejrychlejší způsob, jak vytvořit a vložit data do tabulky jediným příkazem.

VYBRAT... 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]

VYBRAT... INTO neumožňuje změnit metodu distribuce nebo typ indexu jako součást operace. Vytvoříte [dbo].[FactInternetSales_new] pomocí výchozího distribučního typu ROUND_ROBIN a výchozí struktury tabulky CLUSTERED COLUMNSTORE INDEX.

U CTAS můžete na druhou stranu určit rozdělení 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 index pouze v operaci CTAS a zdrojová tabulka je distribuovaná hodnotou hash, zachovejte stejný distribuční sloupec a datový typ. Tím se vyhnete přesunu dat mezi distribucemi během operace, což je efektivnější.

Kopírování tabulky pomocí CTAS

Možná je jedním z nejběžnějších použití CTAS vytvoření kopie tabulky, aby bylo možné změnit DDL. Řekněme, že jste tabulku původně vytvořili jako ROUND_ROBINa teď ji chcete změnit na tabulku distribuovanou ve sloupci. CTAS je způsob, jakým byste změnili distribuční sloupec. CTAS můžete také použít ke změně typů oddílů, indexování nebo sloupců.

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

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 tabulkou Clustered Columnstore Index, abyste mohli využít výkon clusterovaných tabulek Columnstore. Chcete také distribuovat tuto tabulku ProductKey, 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 OrderDateKeyoddíly, 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, aby se v nové tabulce prohodily a potom přehodili starou tabulku.

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

DROP TABLE FactInternetSales_old;

Explicitně stavový datový typ a nulovost výstupu

Při migraci kódu můžete narazit na 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 být správní. Tady je ale skrytý problém.

Následující kód nepřináší 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" předává datový typ a hodnoty nullability výrazu. Přenos datového typu vpřed může vést k drobným rozptylů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 trvalá hodnota ve výsledném sloupci se používá v jiných výrazech, bude chyba ještě důležitě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ší, je problém. Data by se v porovnání se zdrojovým systémem liší a to vede k otázkám integrity migrace. Jedná se o jeden z těch vzácných případů, kdy je "nesprávná" odpověď skutečně správná!

Důvodem, proč vidíme rozdíl mezi těmito dvěma výsledky, je implicitní přetypování typu. V prvním příkladu tabulka definuje definici sloupce. Při vložení řádku dojde k implicitní převodu typu. V 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 není. Když byla tabulka vytvořena v prvním příkladu, byla explicitně definována možnost null sloupce. V druhém příkladu byl výraz ponechán a ve výchozím nastavení by výsledkem byla definice NULL.

Chcete-li tyto problémy vyřešit, musíte explicitně nastavit převod typu a hodnotu null 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

Je potřeba upozornit na následující:

Můžete použít funkci CAST nebo CONVERT.
K vynucení NULLability použijte funkci ISNULL, nikoli COALESCE. Podívejte se na následující poznámku.
ISNULL je nejkrajnější funkce.
Druhá část isNULL je konstanta, 0.

Poznámka:

Aby byla správně nastavená hodnota null, 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. ISNULL se liší. Je to deterministické. Proto, pokud druhá část funkce ISNULL je konstanta nebo literál, 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 množství je však počítaný výraz. Není součástí zdrojových dat.

Pokud chcete vytvořit dělenou datovou sadu, 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 fungoval dokonale dobře. K problému dochází při pokusu o přepnutí oddílu. Definice tabulky se neshodují. Aby se definice tabulky shodovaly, upravte CTAS tak, aby se přidala ISNULL funkce, aby se zachoval 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');

Vidíte, že osvědčeným postupem při vytváření je konzistence typů a udržování vlastností null v objektu CTAS. Pomáhá udržovat integritu výpočtů a také zajišťuje, že přepínání oddílů je možné.

CTAS je jedním z nejdůležitějších tvrzení v Synapse SQL. Ujistěte se, že jste ho důkladně porozuměli. Prohlédněte si dokumentaci k CTAS.

Další kroky

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

Sdílet prostřednictvím

CREATE TABLE AS SELECT (CTAS)