CREATE TABLE AS SELECT

Platí pro:Azure Synapse AnalyticsAnalytics Platform System (PDW)

CREATE TABLE AS SELECT (CTAS) je jednou z nejdůležitějších dostupných funkcí T-SQL. Jde o plně paralelizovanou operaci, která vytvoří novou tabulku na základě výstupu příkazu SELECT. CTAS je nejjednodušší a nejrychlejší způsob, jak vytvořit kopii tabulky.

Pomocí funkce CTAS můžete například:

• Znovu vytvořte tabulku s jiným sloupcem distribuce hodnot hash.
• Znovu vytvořte tabulku jako replikovanou.
• Vytvořte index columnstore jenom u některých sloupců v tabulce.
• Dotazování nebo import externích dat

• Bezserverový fond SQL ve službě Azure Synapse Analytics tuto syntaxi nepodporuje.
• CTAS se podporuje ve skladu v Microsoft Fabric.

Konvence syntaxe jazyka Transact-SQL

Syntaxe

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 
    }

Argumenty

Podrobnosti najdete v části Argumenty v tématu CREATE TABLE.

Možnosti sloupců

column_name [ ,...n ]
Názvy sloupců neumožňují možnosti sloupců uvedené v tématu CREATE TABLE. Místo toho můžete pro novou tabulku zadat volitelný seznam jednoho nebo více názvů sloupců. Sloupce v nové tabulce používají názvy, které zadáte. Když zadáte názvy sloupců, musí se počet sloupců v seznamu sloupců shodovat s počtem sloupců ve výsledcích výběru. Pokud nezadáte žádné názvy sloupců, použije nová cílová tabulka názvy sloupců ve výsledcích příkazu select.

Není možné zadat žádné další možnosti sloupců, jako jsou datové typy, kolace nebo hodnota null. Každý z těchto atributů je odvozen z výsledků SELECT příkazu . Ke změně atributů však můžete použít příkaz SELECT. Příklad najdete v tématu Změna atributů sloupce pomocí CTAS.

Možnosti distribuce tabulky

Podrobnosti a informace o tom, jak zvolit nejlepší sloupec distribuce, najdete v části Možnosti distribuce tabulky v tématu CREATE TABLE. Doporučení, kterou distribuci zvolit pro tabulku na základě skutečného využití nebo ukázkových dotazů, najdete v tématu Poradce pro distribuci v Azure Synapse SQL.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | REPLIKACE Příkaz CTAS vyžaduje možnost distribuce a nemá výchozí hodnoty. To se liší od funkce CREATE TABLE, která má výchozí hodnoty.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Distribuuje řádky na základě hodnot hash až osmi sloupců, což umožňuje rovnoměrnější distribuci dat základní tabulky, snižuje nerovnoměrnou distribuci dat v průběhu času a zlepšuje výkon dotazů.

Podrobnosti a informace o tom, jak zvolit nejlepší sloupec distribuce, najdete v části Možnosti distribuce tabulky v tématu CREATE TABLE.

Možnosti oddílů tabulky

Příkaz CTAS ve výchozím nastavení vytvoří nerozdělenou tabulku, a to i v případě, že je zdrojová tabulka rozdělená na oddíly. Pokud chcete vytvořit dělenou tabulku pomocí příkazu CTAS, musíte zadat možnost oddílu.

Podrobnosti najdete v části Možnosti oddílů tabulky v tématu CREATE TABLE.

Příkaz SELECT

Příkaz SELECT představuje základní rozdíl mezi CTAS a CREATE TABLE.

WITHcommon_table_expression

Určuje dočasnou pojmenovanou sadu výsledků dotazu, která se označuje jako výraz společné tabulky (CTE). Další informace najdete v tématu WITH common_table_expression (Transact-SQL).

SELECTselect_criteria

Naplní novou tabulku výsledky z příkazu SELECT. select_criteria je tělo příkazu SELECT, který určuje, která data se mají zkopírovat do nové tabulky. Informace o příkazech SELECT najdete v tématu SELECT (Transact-SQL).

Nápověda k dotazu

Uživatelé můžou nastavením parametru MAXDOP na celočíselnou hodnotu řídit maximální stupeň paralelismu. Pokud je hodnota MAXDOP nastavená na hodnotu 1, dotaz se spustí v jednom vlákně.

Oprávnění

CTAS vyžaduje SELECT oprávnění ke všem objektům odkazovaným v select_criteria.

Oprávnění k vytvoření tabulky najdete v tématu Oprávnění v tématu CREATE TABLE.

Poznámky

Podrobnosti najdete v tématu Obecné poznámky v tématu CREATE TABLE.

Limity a omezení

Uspořádaný clusterovaný index columnstore je možné vytvořit u sloupců libovolných datových typů podporovaných v Azure Synapse Analytics s výjimkou sloupců řetězců.

SET ROWCOUNT (Transact-SQL) nemá žádný vliv na CTAS. Pokud chcete dosáhnout podobného chování, použijte TOP (Transact-SQL).

Podrobnosti najdete v tématu Omezení a omezení v tématu CREATE TABLE.

Chování při uzamykání

Podrobnosti najdete v tématu Chování zamykání v CREATE TABLE.

Výkon

Pro tabulku distribuovanou hodnotou hash můžete pomocí funkce CTAS zvolit jiný distribuční sloupec, abyste dosáhli lepšího výkonu pro spojení a agregace. Pokud volba jiného distribučního sloupce není vaším cílem, dosáhnete nejlepšího výkonu CTAS, pokud zadáte stejný distribuční sloupec, protože tím zabráníte redistribuci řádků.

Pokud k vytvoření tabulky používáte CTAS a výkon není faktorem, můžete zadat ROUND_ROBIN , abyste nemuseli rozhodovat o distribučním sloupci.

Pokud se chcete vyhnout přesunu dat v následných dotazech, můžete za cenu zvýšeného úložiště zadat REPLICATE načtení úplné kopie tabulky na každém výpočetním uzlu.

Příklady kopírování tabulky

A. Kopírování tabulky pomocí CTAS

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Možná jedním z nejběžnějších CTAS použití je vytvoření kopie tabulky, abyste mohli změnit DDL. Pokud jste například původně vytvořili tabulku jako ROUND_ROBIN a teď ji chcete změnit na tabulku distribuovanou ve sloupci, je postup, CTAS jak byste změnili distribuční sloupec. CTAS lze také použít ke změně dělení, indexování nebo typů sloupců.

Řekněme, že jste tuto tabulku vytvořili zadáním HEAP 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 clusterovaným indexem columnstore, abyste mohli využívat výkon clusterovaných tabulek columnstore. Tuto tabulku ProductKey také chcete distribuovat, 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 oddíly, abyste mohli rychle odstranit stará data vyřazením starých oddílů. Tady je příkaz CTAS, který by zkopíroval 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 pak starou tabulku vypustit.

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

DROP TABLE FactInternetSales_old;

Příklady možností sloupců

B. Změna atributů sloupce pomocí CTAS

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Tento příklad používá CTAS ke změně datových typů, nullability a kolace pro několik sloupců v tabulce 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);

V posledním kroku můžete k přepnutí názvů tabulek použít příkaz RENAME (Transact-SQL ). Díky tomu se dimCustomer2 stává novou tabulkou.

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

DROP TABLE DimCustomer2_old;

Příklady distribuce tabulek

C. Změna metody distribuce tabulky pomocí CTAS

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Tento jednoduchý příklad ukazuje, jak změnit metodu distribuce tabulky. Aby se ukázalo, jak to udělat, změní tabulku distribuovanou hodnotou hash na kruhové dotazování a pak změní tabulku kruhového dotazování zpět na distribuovanou hodnotu hash. Konečná tabulka odpovídá původní tabulce.

Ve většině případů nemusíte měnit tabulku distribuovanou hodnotou hash na tabulku kruhového dotazování. Častěji může být potřeba změnit tabulku kruhového dotazování na distribuovanou tabulku hodnot hash. Můžete například nejprve načíst novou tabulku jako kruhové dotazování a později ji přesunout do distribuované tabulky hash, abyste získali lepší výkon spojení.

V tomto příkladě se používá ukázková databáze AdventureWorksDW. Informace o načtení verze Azure Synapse Analytics najdete v tématu Rychlý start: Vytvoření a dotazování vyhrazeného fondu SQL (dříve SQL DW) v Azure Synapse Analytics pomocí Azure Portal.

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

Potom ho změňte zpět na distribuovanou tabulku hash.

-- 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. Použití CTAS k převodu tabulky na replikovanou tabulku

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Tento příklad platí pro převod distribuovaných tabulek kruhového dotazování nebo hodnoty hash na replikovanou tabulku. Tento konkrétní příklad posouvá předchozí metodu změny typu distribuce o krok dále. Vzhledem k tomu DimSalesTerritory , že se jedná o dimenzi a pravděpodobně menší tabulku, můžete zvolit opětovné vytvoření replikované tabulky, abyste se vyhnuli přesunu dat při připojování k jiným tabulkám.

-- 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. Použití CTAS k vytvoření tabulky s menším počtem sloupců

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Následující příklad vytvoří distribuovanou tabulku kruhového dotazování s názvem myTable (c, ln). Nová tabulka má jenom dva sloupce. Používá aliasy sloupců v příkazu SELECT pro názvy sloupců.

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

Příklady nápovědy k dotazům

F. Použití nápovědy k dotazu s funkcí CREATE TABLE AS SELECT (CTAS)

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Tento dotaz ukazuje základní syntaxi pro použití nápovědy ke spojení dotazů s příkazem CTAS. Po odeslání dotazu použije Azure Synapse Analytics strategii hash join při generování plánu dotazu pro každou jednotlivou distribuci. Další informace o nápovědě dotazu hash join najdete v tématu Option – klauzule (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 );  

Příklady externích tabulek

G. Použití CTAS k importu dat ze služby Azure Blob Storage

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Pokud chcete importovat data z externí tabulky, použijte příkaz CREATE TABLE AS SELECT k výběru z externí tabulky. Syntaxe výběru dat z externí tabulky do Azure Synapse Analytics je stejná jako syntaxe pro výběr dat z běžné tabulky.

Následující příklad definuje externí tabulku dat v účtu Azure Blob Storage. Pak pomocí příkazu CREATE TABLE AS SELECT vybere z externí tabulky. Importuje data z Azure Blob Storage textem oddělených souborů a uloží je do nové tabulky 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. Import dat Hadoopu z externí tabulky pomocí CTAS

Platí pro: PDW (Analytics Platform System)

Pokud chcete importovat data z externí tabulky, jednoduše pomocí příkazu CREATE TABLE AS SELECT vyberte z externí tabulky. Syntaxe výběru dat z externí tabulky do pdw (Analytics Platform System) je stejná jako syntaxe pro výběr dat z běžné tabulky.

Následující příklad definuje externí tabulku v clusteru Hadoop. Pak pomocí příkazu CREATE TABLE AS SELECT vybere z externí tabulky. Tato možnost importuje data ze souborů Hadoop oddělených textem a uloží je do nové tabulky PDW (Analytics Platform System).

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

Příklady použití CTAS k nahrazení kódu SQL Server

Pomocí CTAS můžete obejít některé nepodporované funkce. Kromě toho, že je možné spustit kód v datovém skladu, může přepsání existujícího kódu na použití CTAS obvykle zlepšit výkon. Je to výsledek plně paralelizovaného návrhu.

I. Místo příkazu SELECT použijte CTAS. DO

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

SQL Server kód obvykle používá příkaz SELECT.. INTO k naplnění tabulky výsledky příkazu SELECT. Toto je příklad SQL Server SELECT. Příkaz INTO.

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

Tato syntaxe není podporována v Azure Synapse Analytics a Paralelní Data Warehouse. Tento příklad ukazuje, jak přepsat předchozí příkaz SELECT. Příkaz INTO jako příkaz CTAS. Můžete zvolit kteroukoli z možností DISTRIBUCE popsaných v syntaxi CTAS. V tomto příkladu se používá metoda distribuce ROUND_ROBIN.

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

J. Zjednodušení příkazů sloučení pomocí CTAS

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Příkazy sloučení lze nahradit, alespoň částečně, pomocí příkazu CTAS. Můžete konsolidovat INSERT a do UPDATE jednoho příkazu. Všechny odstraněné záznamy by bylo potřeba uzavřít v druhém příkazu.

Příklad UPSERT :

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. Explicitní stavový datový typ a dostupnost výstupu s hodnotou null

Platí pro: Azure Synapse Analytics and Analytics Platform System (PDW)

Při migraci kódu SQL Server do Azure Synapse Analytics můžete zjistit, že se setkáte s tímto typem 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
;

Instinktivně si můžete myslet, ž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 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řenáší hodnoty datového typu a hodnoty null výrazu. Pokud nejste opatrní, může to vést k drobným odchylkám v hodnotách.

Vyzkoušejte následující příklad:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Hodnota uložená pro výsledek se liší. S tím, jak se trvalá hodnota ve sloupci výsledku používá v jiných výrazech, je chyba ještě významnější.

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á!

Důvodem, proč vidíme tento rozdíl mezi 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 je definovaný jako sloupec NULLable, zatímco v prvním příkladu ne. Při vytvoření tabulky v prvním příkladu sloupce byla explicitně definována hodnota nullability. Ve druhém příkladu byla ponechána na výrazu a ve výchozím nastavení by výsledkem byla NULL definice.

Chcete-li tyto problémy vyřešit, musíte explicitně nastavit převod typu a hodnotu null v SELECT části CTAS příkazu. Tyto vlastnosti nemůžete nastavit v části pro vytvoření tabulky.

Tento 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

V příkladu si všimněte následujícího:

• Bylo možné použít funkci CAST nebo CONVERT.
• Funkce ISNULL se používá k vynucení, že funkce NULLability není COALESCE.
• FUNKCE ISNULL je vnější funkce.
• Druhá část knihovny ISNULL je konstanta , 0.

Tento tip není užitečný jenom k zajištění integrity výpočtů. Je to také důležité pro přepínání oddílů tabulky. Představte si, že máte tuto tabulku definovanou jako 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 hodnoty je ale počítaný výraz, který není součástí zdrojových dat.

Pokud chcete vytvořit dělenou datovou sadu, podívejte se na následující příklad:

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 naprosto v pořádku. K problému dochází při pokusu o přepnutí oddílů. Definice tabulek se neshodují. Aby bylo možné vytvořit definice tabulky, je potřeba upravit shodu s 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');

Vidíte proto, že dobrým technickým osvědčeným postupem je konzistence typů a udržování vlastností nullability u CTAS. Pomáhá udržovat integritu ve výpočtech a také zajišťuje, že je možné přepínat oddíly.

L. Vytvoření uspořádaného clusterovaného indexu columnstore s využitím MAXDOP 1

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

Další kroky

• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE EXTERNAL TABLE AS SELECT (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)

Platí pro:Sklad v Microsoft Fabric

CREATE TABLE AS SELECT (CTAS) je jednou z nejdůležitějších dostupných funkcí T-SQL. Jde o plně paralelizovanou operaci, která vytvoří novou tabulku na základě výstupu příkazu SELECT. CTAS je nejjednodušší a nejrychlejší způsob, jak vytvořit kopii tabulky.

Například pomocí CTAS ve skladu v Microsoft Fabric můžete:

• Vytvořte kopii tabulky s některými sloupci zdrojové tabulky.
• Vytvořte tabulku, která je výsledkem dotazu, který spojuje jiné tabulky.

Další informace o používání CTAS ve skladu v Microsoft Fabric najdete v tématu Ingestování dat do skladu pomocí TSQL.

Konvence syntaxe jazyka Transact-SQL

Syntaxe

CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    AS <select_statement>  
[;]  

<select_statement> ::=  
    SELECT select_criteria  

Argumenty

Podrobnosti najdete v tématu Argumenty v tématu CREATE TABLE pro Microsoft Fabric.

Možnosti sloupců

column_name [ ,...n ]
Názvy sloupců neumožňují možnosti sloupců uvedené v tématu CREATE TABLE. Místo toho můžete pro novou tabulku zadat volitelný seznam jednoho nebo více názvů sloupců. Sloupce v nové tabulce používají názvy, které zadáte. Když zadáte názvy sloupců, musí se počet sloupců v seznamu sloupců shodovat s počtem sloupců ve výsledcích výběru. Pokud nezadáte žádné názvy sloupců, použije nová cílová tabulka názvy sloupců ve výsledcích příkazu select.

Není možné zadat žádné další možnosti sloupců, jako jsou datové typy, kolace nebo hodnota null. Každý z těchto atributů je odvozen z výsledků SELECT příkazu . Ke změně atributů však můžete použít příkaz SELECT.

Příkaz SELECT

Příkaz SELECT představuje základní rozdíl mezi CTAS a CREATE TABLE.

SELECTselect_criteria

Naplní novou tabulku výsledky z příkazu SELECT. select_criteria je tělo příkazu SELECT, který určuje, která data se mají zkopírovat do nové tabulky. Informace o příkazech SELECT najdete v tématu SELECT (Transact-SQL).

Oprávnění

CTAS vyžaduje SELECT oprávnění ke všem objektům odkazovaným v select_criteria.

Oprávnění k vytvoření tabulky najdete v tématu Oprávnění v tématu CREATE TABLE.

Poznámky

Podrobnosti najdete v tématu Obecné poznámky v tématu CREATE TABLE.

Limity a omezení

SET ROWCOUNT (Transact-SQL) nemá žádný vliv na CTAS. Pokud chcete dosáhnout podobného chování, použijte TOP (Transact-SQL).

Podrobnosti najdete v tématu Omezení a omezení v tématu CREATE TABLE.

Chování při uzamykání

Podrobnosti najdete v tématu Chování zamykání v CREATE TABLE.

Příklady kopírování tabulky

Další informace o používání CTAS ve skladu v Microsoft Fabric najdete v tématu Ingestování dat do skladu pomocí TSQL.

A. Změna atributů sloupce pomocí CTAS

Tento příklad používá CTAS ke změně datových typů a nullability pro několik sloupců v tabulce DimCustomer2 .

-- Original table 
CREATE TABLE [dbo].[DimCustomer2] (  
    [CustomerKey] INT NOT NULL,  
    [GeographyKey] INT NULL,  
    [CustomerAlternateKey] VARCHAR(15)NOT NULL  
)  

-- CTAS example to change data types and nullability of columns
CREATE TABLE test  
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,  
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] VARCHAR(15) NOT NULL, 
    [CustomerAlternateKeyNullable] VARCHAR(15) NULL, 
NOT NULL
)

B. Použití CTAS k vytvoření tabulky s menším počtem sloupců

Následující příklad vytvoří tabulku s názvem myTable (c, ln). Nová tabulka má jenom dva sloupce. Používá aliasy sloupců v příkazu SELECT pro názvy sloupců.

CREATE TABLE myTable  
AS SELECT CustomerKey AS c, LastName AS ln  
    FROM dimCustomer; 

C. Místo příkazu SELECT použijte CTAS. DO

SQL Server kód obvykle používá příkaz SELECT.. INTO k naplnění tabulky výsledky příkazu SELECT. Toto je příklad SQL Server SELECT. Příkaz INTO.

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

Tento příklad ukazuje, jak přepsat předchozí příkaz SELECT. Příkaz INTO jako příkaz CTAS.

CREATE TABLE NewFactTable
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
;

D. Zjednodušení příkazů sloučení pomocí CTAS

Příkazy sloučení lze nahradit, alespoň částečně, pomocí příkazu CTAS. Můžete konsolidovat INSERT a do UPDATE jednoho příkazu. Všechny odstraněné záznamy by bylo potřeba uzavřít v druhém příkazu.

Příklad UPSERT :

CREATE TABLE dbo.[DimProduct_upsert]
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]
)
;

E. Explicitně stavový datový typ a nullability výstupu

Při migraci kódu SQL Server do služby Warehouse můžete zjistit, že používáte tento typ modelu kódování:

DECLARE @d DECIMAL(7,2) = 85.455
,       @f FLOAT(24)    = 85.455

CREATE TABLE result (result DECIMAL(7,2) NOT NULL)

INSERT INTO result
SELECT @d*@f
;

Instinktivně si můžete myslet, že byste tento kód měli migrovat do CTAS a měli byste pravdu. 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
AS
SELECT @d*@f as result
;

Všimněte si, že sloupec "result" přenáší datové typy a hodnoty nullability výrazu. To může vést k drobným odchylkám v hodnotách, pokud nejste opatrní.

Jako příklad použijte následující:

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 v jiných výrazech použije trvalá hodnota ve sloupci výsledků, bude chyba ještě významnější.

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

Důvodem, proč vidíme tento rozdíl mezi 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 je definovaný jako sloupec NULLable, zatímco v prvním příkladu ne. Při vytvoření tabulky v prvním příkladu sloupce byla explicitně definována hodnota null. Ve druhém příkladu byla ponechána na výrazu a ve výchozím nastavení by výsledkem byla NULL definice.

Chcete-li tyto problémy vyřešit, musíte explicitně nastavit převod typů a nullability v SELECT části CTAS příkazu. Tyto vlastnosti nemůžete nastavit v části vytvořit tabulku.

Tento příklad ukazuje, jak opravit kód:

DECLARE @d DECIMAL(7,2) = 85.455
,       @f FLOAT(24)    = 85.455

CREATE TABLE ctas_r
AS
SELECT ISNULL(CAST(@d*@f AS DECIMAL(7,2)),0) as result

V příkladu si všimněte následujícího:

• Bylo možné použít přetypování nebo převod.
• FUNKCE ISNULL se používá k vynucení možnosti NULLability, nikoli COALESCE.
• ISNULL je nejkrajnější funkce.
• Druhá část hodnoty ISNULL je konstanta 0.

Tento tip není užitečný jen pro 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 fakt:

CREATE TABLE [dbo].[Sales]
(
    [date]      INT     NOT NULL
,   [product]   INT     NOT NULL
,   [store]     INT     NOT NULL
,   [quantity]  INT     NOT NULL
,   [price]     DECIMAL(7,2)   NOT NULL
,   [amount]    DECIMAL(7,2)   NOT NULL
)
;

Pole hodnoty je ale počítaný výraz, který není součástí zdrojových dat.

Vidíte proto, že dobrým technickým osvědčeným postupem je konzistence typů a udržování vlastností nullability na CTAS. Pomáhá udržovat integritu ve výpočtech.

Další kroky

• Vytváření tabulek ve službě Warehouse v Microsoft Fabric
• Co jsou datové sklady v Microsoft Fabric?
• Ingestování dat do skladu pomocí TSQL