CREATE TABLE AS SELECT

Artikel
05/23/2023

Gäller för:Azure Synapse Analytics Analytics Platform System (PDW)

CREATE TABLE AS SELECT (CTAS) är en av de viktigaste T-SQL-funktionerna som är tillgängliga. Det här är en fullständigt parallelliserad åtgärd som skapar en ny tabell baserat på resultatet av en SELECT-instruktion. CTAS är det enklaste och snabbaste sättet att skapa en kopia av en tabell.

Använd till exempel CTAS för att:

Återskapa en tabell med en annan hashdistributionskolumn.
Återskapa en tabell som replikerad.
Skapa ett kolumnlagringsindex på bara några av kolumnerna i tabellen.
Fråga eller importera externa data.

Anteckning

Eftersom CTAS lägger till funktionerna för att skapa en tabell försöker det här avsnittet inte upprepa create table-ämnet. I stället beskrivs skillnaderna mellan CTAS- och CREATE TABLE-instruktionerna. Information om CREATE TABLE finns i CREATE TABLE-instruktionen (Azure Synapse Analytics).

Den här syntaxen stöds inte av en serverlös SQL-pool i Azure Synapse Analytics.
CTAS stöds i lagret i Microsoft Fabric.

Transact-SQL-syntaxkonventioner

Syntax

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 
    }

Argument

Mer information finns i avsnittet Argument i CREATE TABLE.

Kolumnalternativ

column_name [ ,...n ]
Kolumnnamn tillåter inte de kolumnalternativ som anges i CREATE TABLE. I stället kan du ange en valfri lista med ett eller flera kolumnnamn för den nya tabellen. Kolumnerna i den nya tabellen använder de namn som du anger. När du anger kolumnnamn måste antalet kolumner i kolumnlistan matcha antalet kolumner i urvalsresultatet. Om du inte anger några kolumnnamn använder den nya måltabellen kolumnnamnen i select-instruktionsresultatet.

Du kan inte ange några andra kolumnalternativ, till exempel datatyper, sortering eller nullbarhet. Vart och ett av dessa attribut härleds från resultatet av -instruktionen SELECT . Du kan dock använda SELECT-instruktionen för att ändra attributen. Ett exempel finns i Använda CTAS för att ändra kolumnattribut.

Alternativ för tabelldistribution

Mer information och information om hur du väljer den bästa distributionskolumnen finns i avsnittet Tabelldistributionsalternativ i SKAPA TABELL. Rekommendationer om vilken distribution som ska väljas för en tabell baserat på faktisk användning eller exempelfrågor finns i Distribution Advisor i Azure Synapse SQL.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | REPLIKERA CTAS-instruktionen kräver ett distributionsalternativ och har inte standardvärden. Detta skiljer sig från CREATE TABLE, som har standardvärden.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Distribuerar raderna baserat på hash-värden för upp till åtta kolumner, vilket möjliggör en jämnare fördelning av bastabelldata, vilket minskar dataskevheten över tid och förbättrar frågeprestanda.

Anteckning

Om du vill aktivera funktionen ändrar du databasens kompatibilitetsnivå till 50 med det här kommandot. Mer information om hur du anger databasens kompatibilitetsnivå finns i ALTER DATABASE SCOPED CONFIGURATION (ÄNDRA DATABASOMFATTNINGSKONFIGURATION). Exempelvis: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
Om du vill inaktivera funktionen för distribution med flera kolumner (MCD) kör du det här kommandot för att ändra databasens kompatibilitetsnivå till AUTO. Exempel: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; Befintliga MCD-tabeller förblir men blir oläsliga. Frågor över MCD-tabeller returnerar det här felet: Related table/view is not readable because it distributes data on multiple columns and multi-column distribution is not supported by this product version or this feature is disabled.
- Aktivera funktionen igen om du vill få åtkomst till MCD-tabeller igen.
- Om du vill läsa in data i en MCD-tabell använder du CTAS-instruktionen och datakällan måste vara Synapse SQL-tabeller.
- CTAS på MCD HEAP-måltabeller stöds inte. Använd i stället INSERT SELECT som en lösning för att läsa in data i MCD HEAP-tabeller.
Användning av SSMS för att generera ett skript för att skapa MCD-tabeller stöds för närvarande utöver SSMS version 19.

Mer information och information om hur du väljer den bästa distributionskolumnen finns i avsnittet Tabelldistributionsalternativ i SKAPA TABELL.

Alternativ för tabellpartition

CTAS-instruktionen skapar som standard en icke-partitionerad tabell, även om källtabellen är partitionerad. Om du vill skapa en partitionerad tabell med CTAS-instruktionen måste du ange partitionsalternativet.

Mer information finns i avsnittet Alternativ för tabellpartition i CREATE TABLE.

SELECT-instruktion

SELECT-instruktionen är den grundläggande skillnaden mellan CTAS och CREATE TABLE.

`WITH`common_table_expression

Anger en tillfällig namngiven resultatuppsättning, som kallas för ett gemensamt tabelluttryck (CTE). Mer information finns i WITH common_table_expression (Transact-SQL).

`SELECT`select_criteria

Fyller i den nya tabellen med resultatet från en SELECT-instruktion. select_criteria är brödtexten i SELECT-instruktionen som avgör vilka data som ska kopieras till den nya tabellen. Information om SELECT-instruktioner finns i SELECT (Transact-SQL).

Frågetips

Användare kan ange MAXDOP till ett heltalsvärde för att styra den maximala graden av parallellitet. När MAXDOP är inställt på 1 körs frågan av en enda tråd.

Behörigheter

CTAS kräver SELECT behörighet för alla objekt som refereras i select_criteria.

Behörigheter för att skapa en tabell finns i Behörigheter i SKAPA TABELL.

Kommentarer

Mer information finns i Allmänna kommentarer i CREATE TABLE.

Begränsningar

Ett grupperat kolumnlagringsindex kan skapas på kolumner av alla datatyper som stöds i Azure Synapse Analytics förutom för strängkolumner.

SET ROWCOUNT (Transact-SQL) har ingen effekt på CTAS. Använd TOP (Transact-SQL) för att uppnå ett liknande beteende.

Mer information finns i Begränsningar och begränsningar i CREATE TABLE.

Låsningsbeteende

Mer information finns i Låsbeteende i CREATE TABLE.

Prestanda

För en hash-distribuerad tabell kan du använda CTAS för att välja en annan distributionskolumn för att få bättre prestanda för kopplingar och aggregeringar. Om du inte väljer en annan distributionskolumn får du bästa möjliga CTAS-prestanda om du anger samma distributionskolumn eftersom det undviker att omdistribuera raderna.

Om du använder CTAS för att skapa tabeller och prestanda inte är en faktor kan du ange ROUND_ROBIN för att undvika att behöva välja en distributionskolumn.

För att undvika dataflytt i efterföljande frågor kan du ange REPLICATE på bekostnad av ökad lagring för att läsa in en fullständig kopia av tabellen på varje beräkningsnod.

Exempel för att kopiera en tabell

A. Använda CTAS för att kopiera en tabell

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

En av de vanligaste användningsområdena CTAS för är kanske att skapa en kopia av en tabell så att du kan ändra DDL. Om du till exempel ursprungligen skapade tabellen som ROUND_ROBIN och nu vill ändra den till en tabell som distribueras i en kolumn, CTAS så ändrar du distributionskolumnen. CTAS kan också användas för att ändra partitionering, indexering eller kolumntyper.

Anta att du har skapat den här tabellen genom att HEAP ange och använda standarddistributionstypen 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 vill du skapa en ny kopia av den här tabellen med ett grupperat kolumnlagringsindex så att du kan dra nytta av prestanda för klustrade kolumnlagringstabeller. Du vill också distribuera den här tabellen eftersom ProductKey du förväntar dig kopplingar i den här kolumnen och vill undvika dataflytt under kopplingar på ProductKey. Slutligen vill du också lägga till partitionering på OrderDateKey så att du snabbt kan ta bort gamla data genom att släppa gamla partitioner. Här är CTAS-instruktionen som kopierar din gamla tabell till en ny tabell:

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;

Slutligen kan du byta namn på dina tabeller så att de växlar i den nya tabellen och sedan ta bort den gamla tabellen.

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

DROP TABLE FactInternetSales_old;

Exempel på kolumnalternativ

B. Använda CTAS för att ändra kolumnattribut

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

I det här exemplet används CTAS för att ändra datatyper, nullbarhet och sortering för flera kolumner i DimCustomer2 tabellen.

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

Som ett sista steg kan du använda RENAME (Transact-SQL) för att växla tabellnamn. Detta gör DimCustomer2 till den nya tabellen.

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

DROP TABLE DimCustomer2_old;

Exempel för tabelldistribution

C. Använda CTAS för att ändra distributionsmetoden för en tabell

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

Det här enkla exemplet visar hur du ändrar distributionsmetoden för en tabell. För att visa hur du gör detta ändrar den en hash-distribuerad tabell till resursallokering och ändrar sedan round-robin-tabellen tillbaka till hash-distribuerad. Den slutliga tabellen matchar den ursprungliga tabellen.

I de flesta fall behöver du inte ändra en hash-distribuerad tabell till en resursallokeringstabell. Oftare kan du behöva ändra en resursallokeringstabell till en hash-distribuerad tabell. Du kan till exempel först läsa in en ny tabell som resursallokering och sedan flytta den till en hash-distribuerad tabell för att få bättre kopplingsprestanda.

Det här exemplet använder exempeldatabasen AdventureWorksDW. Information om hur du läser in Azure Synapse Analytics-versionen finns i Snabbstart: Skapa och fråga en dedikerad SQL-pool (tidigare SQL DW) i Azure Synapse Analytics med hjälp av 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];

Ändra sedan tillbaka den till en hash-distribuerad tabell.

-- 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. Använda CTAS för att konvertera en tabell till en replikerad tabell

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

Det här exemplet gäller för konvertering av round-robin- eller hash-distributed-tabeller till en replikerad tabell. Det här exemplet tar den tidigare metoden att ändra distributionstypen ett steg längre. Eftersom DimSalesTerritory är en dimension och troligen en mindre tabell kan du välja att återskapa tabellen som replikerad för att undvika dataflytt när du ansluter till andra tabeller.

-- 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. Använda CTAS för att skapa en tabell med färre kolumner

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

I följande exempel skapas en distribuerad tabell med resursallokering med namnet myTable (c, ln). Den nya tabellen har bara två kolumner. Den använder kolumnaliasen i SELECT-instruktionen för namnen på kolumnerna.

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

Exempel på frågetips

F. Använda ett frågetips med CREATE TABLE AS SELECT (CTAS)

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

Den här frågan visar den grundläggande syntaxen för att använda ett frågekopplingstips med CTAS-instruktionen. När frågan har skickats tillämpar Azure Synapse Analytics hashkopplingsstrategin när den genererar frågeplanen för varje enskild distribution. Mer information om hash-kopplingsfrågans tips finns i OPTION-satsen (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 );

Exempel för externa tabeller

G. Använda CTAS för att importera data från Azure Blob Storage

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

Om du vill importera data från en extern tabell använder du CREATE TABLE AS SELECT för att välja från den externa tabellen. Syntaxen för att välja data från en extern tabell till Azure Synapse Analytics är samma som syntaxen för att välja data från en vanlig tabell.

I följande exempel definieras en extern tabell för data i ett Azure Blob Storage-konto. Den använder sedan CREATE TABLE AS SELECT för att välja från den externa tabellen. Detta importerar data från Azure Blob Storage textavgränsade filer och lagrar data i en ny Azure Synapse Analytics-tabell.

--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. Använda CTAS för att importera Hadoop-data från en extern tabell

Gäller för: Analytics Platform System (PDW)

Om du vill importera data från en extern tabell använder du bara CREATE TABLE AS SELECT för att välja från den externa tabellen. Syntaxen för att välja data från en extern tabell till Analytics Platform System (PDW) är samma som syntaxen för att välja data från en vanlig tabell.

I följande exempel definieras en extern tabell i ett Hadoop-kluster. Den använder sedan CREATE TABLE AS SELECT för att välja från den externa tabellen. Detta importerar data från hadoop-textavgränsade filer och lagrar data i en ny analysplattformssystemtabell (PDW).

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

Exempel som använder CTAS för att ersätta SQL Server kod

Använd CTAS för att kringgå vissa funktioner som inte stöds. Förutom att kunna köra koden på informationslagret förbättras vanligtvis prestandan om du skriver om befintlig kod för att använda CTAS. Detta är ett resultat av dess fullständigt parallelliserade design.

Anteckning

Försök att tänka "CTAS först". Om du tror att du kan lösa ett problem med det CTAS är det vanligtvis det bästa sättet att hantera det – även om du skriver mer data som ett resultat.

I. Använd CTAS i stället för SELECT.. I

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

SQL Server koden använder vanligtvis SELECT.. INTO för att fylla i en tabell med resultatet av en SELECT-instruktion. Det här är ett exempel på en SQL Server SELECT.. INTO-instruktion.

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

Den här syntaxen stöds inte i Azure Synapse Analytics och Parallel Data Warehouse. Det här exemplet visar hur du skriver om föregående SELECT.. INTO-instruktion som en CTAS-instruktion. Du kan välja något av distributionsalternativen som beskrivs i CTAS-syntaxen. I det här exemplet används distributionsmetoden ROUND_ROBIN.

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

J. Använda CTAS för att förenkla sammanslagningsinstruktioner

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

Sammanslagningsinstruktioner kan ersättas, åtminstone delvis, med hjälp CTASav . Du kan konsolidera INSERT och UPDATE till en enda -instruktion. Alla borttagna poster måste stängas av i en andra instruktion.

Ett exempel på följande 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. Uttryckligen tillståndsdatatyp och nullbarhet för utdata

Gäller för: Azure Synapse Analytics and Analytics Platform System (PDW)

När du migrerar SQL Server kod till Azure Synapse Analytics kan du stöta på den här typen av kodningsmönster:

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
;

Instinktivt kanske du tycker att du ska migrera den här koden till en CTAS och du skulle ha rätt. Det finns dock ett dolt problem här.

Följande kod ger INTE samma resultat:

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
;

Observera att kolumnen "result" vidarebefordrar datatypen och nullability-värdena för uttrycket. Detta kan leda till diskreta avvikelser i värden om du inte är försiktig.

Prova följande som exempel:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Värdet som lagras för resultatet är annorlunda. När det beständiga värdet i resultatkolumnen används i andra uttryck blir felet ännu viktigare.

En skärmbild från SQL Server Management Studio (SSMS) av resultatet CREATE TABLE AS SELECT.

Detta är viktigt för datamigreringar. Även om den andra frågan förmodligen är mer exakt finns det ett problem. Data skiljer sig från källsystemet och leder till integritetsfrågor vid migreringen. Detta är ett av de sällsynta fall där "fel" svar faktiskt är rätt!

Anledningen till att vi ser den här skillnaden mellan de två resultaten beror på implicit typgjutning. I det första exemplet definierar tabellen kolumndefinitionen. När raden infogas sker en implicit typkonvertering. I det andra exemplet finns det ingen implicit typkonvertering eftersom uttrycket definierar kolumnens datatyp. Observera också att kolumnen i det andra exemplet har definierats som en NULLable-kolumn medan den i det första exemplet inte har det. När tabellen skapades i den första exempelkolumnen definierades nullbarhet uttryckligen. I det andra exemplet lämnades det till uttrycket och som standard skulle detta resultera i en NULL definition.

För att lösa dessa problem måste du uttryckligen ange typkonvertering och nullbarhet i SELECT delen av -instruktionen CTAS . Du kan inte ange dessa egenskaper i skapa tabelldelen.

Det här exemplet visar hur du åtgärdar koden:

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

Observera följande i exemplet:

CAST eller CONVERT kunde ha använts.
ISNULL används för att tvinga NULLability inte COALESCE.
ISULL är den yttersta funktionen.
Den andra delen av ISNULL är en konstant, 0.

Anteckning

För att null-värdet ska vara korrekt inställt är det viktigt att använda ISNULL och inte COALESCE. COALESCE är inte en deterministisk funktion och därför kommer resultatet av uttrycket alltid att vara NULLable. ISNULL är annorlunda. Det är deterministiskt. När den andra delen av ISNULL funktionen är en konstant eller en literal blir därför det resulterande värdet INTE NULL.

Det här tipset är inte bara användbart för att säkerställa integriteten i dina beräkningar. Det är också viktigt för växling av tabellpartitioner. Anta att du har den här tabellen definierad som ditt faktum:

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

Värdefältet är dock ett beräknat uttryck som inte ingår i källdata.

Tänk på följande exempel för att skapa din partitionerade datauppsättning:

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

Frågan skulle köras perfekt. Problemet uppstår när du försöker utföra partitionsväxeln. Tabelldefinitionerna matchar inte. Om du vill göra tabelldefinitionerna matchar du CTAS måste ändras.

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

Därför kan du se att typkonsekvens och att upprätthålla nullabilitetsegenskaper på en CTAS är en bra metod för att skapa lösningar. Det bidrar till att upprätthålla integriteten i dina beräkningar och säkerställer också att partitionsväxling är möjlig.

L. Skapa ett ordnat grupperat columnstore-index med MAXDOP 1

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

Nästa steg

Gäller för:Informationslager i Microsoft Fabric

Använd till exempel CTAS i Warehouse i Microsoft Fabric för att:

Skapa en kopia av en tabell med några av kolumnerna i källtabellen.
Skapa en tabell som är resultatet av en fråga som ansluter till andra tabeller.

Mer information om hur du använder CTAS på ditt lager i Microsoft Fabric finns i Mata in data i ditt lager med TSQL.

Anteckning

Eftersom CTAS lägger till funktioner för att skapa en tabell försöker det här avsnittet inte upprepa create table-ämnet. I stället beskrivs skillnaderna mellan CTAS- och CREATE TABLE-uttrycken. Information om CREATE TABLE finns i CREATE TABLE-instruktionen .

Transact-SQL-syntaxkonventioner

Syntax

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

<select_statement> ::=  
    SELECT select_criteria

Argument

Mer information finns i Argument i CREATE TABLE för Microsoft Fabric.

Kolumnalternativ

SELECT-instruktion

SELECT-instruktionen är den grundläggande skillnaden mellan CTAS och CREATE TABLE.

`SELECT`select_criteria

Behörigheter

CTAS kräver SELECT behörighet för alla objekt som refereras i select_criteria.

Behörigheter för att skapa en tabell finns i Behörigheter i SKAPA TABELL.

Kommentarer

Mer information finns i Allmänna kommentarer i CREATE TABLE.

Begränsningar

SET ROWCOUNT (Transact-SQL) har ingen effekt på CTAS. Om du vill uppnå ett liknande beteende använder du TOP (Transact-SQL).

Mer information finns i Begränsningar och begränsningar i CREATE TABLE.

Låsbeteende

Mer information finns i Låsbeteende i CREATE TABLE.

Exempel för att kopiera en tabell

Mer information om hur du använder CTAS på ditt lager i Microsoft Fabric finns i Mata in data i ditt lager med TSQL.

A. Använda CTAS för att ändra kolumnattribut

I det här exemplet används CTAS för att ändra datatyper och nullbarhet för flera kolumner i DimCustomer2 tabellen.

-- 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. Använda CTAS för att skapa en tabell med färre kolumner

I följande exempel skapas en tabell med namnet myTable (c, ln). Den nya tabellen har bara två kolumner. Den använder kolumnaliasen i SELECT-instruktionen för namnen på kolumnerna.

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

C. Använd CTAS i stället för SELECT.. I

SQL Server kod använder vanligtvis SELECT.. INTO för att fylla i en tabell med resultatet av en SELECT-instruktion. Det här är ett exempel på en SQL Server SELECT.. INTO-instruktion.

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

Det här exemplet visar hur du skriver om föregående SELECT.. INTO-instruktion som en CTAS-instruktion.

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

D. Använda CTAS för att förenkla sammanslagningsinstruktioner

Sammanslagningsinstruktioner kan ersättas, åtminstone delvis, med hjälp CTASav . Du kan konsolidera INSERT och UPDATE till en enda instruktion. Alla borttagna poster måste stängas av i en andra instruktion.

Ett exempel på följande 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. Uttryckligen tillståndsdatatyp och nullbarhet för utdata

När du migrerar SQL Server kod till Warehouse kan du stöta på den här typen av kodningsmönster:

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
;

Instinktivt kanske du tycker att du ska migrera den här koden till en CTAS och du skulle ha rätt. Det finns dock ett dolt problem här.

Följande kod ger INTE samma resultat:

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

CREATE TABLE ctas_r
AS
SELECT @d*@f as result
;

Observera att kolumnen "result" vidarebefordrar datatypen och nullability-värdena för uttrycket. Detta kan leda till diskreta avvikelser i värden om du inte är försiktig.

Prova följande som exempel:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

Värdet som lagras för resultatet är annorlunda. När det beständiga värdet i resultatkolumnen används i andra uttryck blir felet ännu viktigare.

En skärmbild från SQL Server Management Studio (SSMS) av resultatet CREATE TABLE AS SELECT.

Anledningen till att vi ser den här skillnaden mellan de två resultaten beror på implicit typgjutning. I det första exemplet definierar tabellen kolumndefinitionen. När raden infogas sker en implicit typkonvertering. I det andra exemplet finns det ingen implicit typkonvertering eftersom uttrycket definierar kolumnens datatyp. Observera också att kolumnen i det andra exemplet har definierats som en NULLable-kolumn medan den i det första exemplet inte har det. När tabellen skapades i den första exempelkolumnen definierades nullbarhet uttryckligen. I det andra exemplet lämnades det till uttrycket och det skulle som standard resultera i en NULL definition.

För att lösa dessa problem måste du uttryckligen ange typkonvertering och nullbarhet i SELECT delen av -instruktionen CTAS . Du kan inte ange dessa egenskaper i create-tabelldelen.

Det här exemplet visar hur du åtgärdar koden:

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

Observera följande i exemplet:

CAST eller KONVERTERA kunde ha använts.
ISULL används för att tvinga NULLability inte COALESCE.
ISNULL är den yttersta funktionen.
Den andra delen av ISNULL är en konstant, 0.

Anteckning

För att nullbarheten ska vara korrekt inställd är det viktigt att använda ISNULL och inte COALESCE. COALESCE är inte en deterministisk funktion och därför kommer resultatet av uttrycket alltid att vara NULLable. ISNULL är annorlunda. Det är deterministiskt. När den andra delen av ISNULL funktionen är en konstant eller en literal blir därför det resulterande värdet INTE NULL.

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

Värdefältet är dock ett beräknat uttryck som inte ingår i källdata.

Därför kan du se att typkonsekvens och att upprätthålla nullbarhetsegenskaper på en CTAS är en bra metod för teknik. Det hjälper till att upprätthålla integriteten i dina beräkningar.

CREATE TABLE AS SELECT

Syntax

Argument

Kolumnalternativ

Alternativ för tabelldistribution

Alternativ för tabellpartition

SELECT-instruktion

WITHcommon_table_expression

SELECTselect_criteria

Frågetips

Behörigheter

Kommentarer

Begränsningar

Låsningsbeteende

Prestanda

Exempel för att kopiera en tabell

A. Använda CTAS för att kopiera en tabell

Exempel på kolumnalternativ

B. Använda CTAS för att ändra kolumnattribut

Exempel för tabelldistribution

C. Använda CTAS för att ändra distributionsmetoden för en tabell

D. Använda CTAS för att konvertera en tabell till en replikerad tabell

E. Använda CTAS för att skapa en tabell med färre kolumner

Exempel på frågetips

F. Använda ett frågetips med CREATE TABLE AS SELECT (CTAS)

Exempel för externa tabeller

G. Använda CTAS för att importera data från Azure Blob Storage

H. Använda CTAS för att importera Hadoop-data från en extern tabell

Exempel som använder CTAS för att ersätta SQL Server kod

I. Använd CTAS i stället för SELECT.. I

J. Använda CTAS för att förenkla sammanslagningsinstruktioner

K. Uttryckligen tillståndsdatatyp och nullbarhet för utdata

L. Skapa ett ordnat grupperat columnstore-index med MAXDOP 1

Nästa steg

Syntax

Argument

Kolumnalternativ

SELECT-instruktion

SELECTselect_criteria

Behörigheter

Kommentarer

Begränsningar

Låsbeteende

Exempel för att kopiera en tabell

A. Använda CTAS för att ändra kolumnattribut

B. Använda CTAS för att skapa en tabell med färre kolumner

C. Använd CTAS i stället för SELECT.. I

D. Använda CTAS för att förenkla sammanslagningsinstruktioner

E. Uttryckligen tillståndsdatatyp och nullbarhet för utdata

Nästa steg

Feedback

Feedback

Ytterligare resurser

`WITH`common_table_expression

`SELECT`select_criteria

`SELECT`select_criteria