CREATE TABLE AS SELECT (CTAS)

Dieser Artikel erläutert die Anweisung CREATE TABLE AS SELECT (CTAS) im dedizierten SQL-Pool (ehemals SQL DW) zum Entwickeln von Lösungen. Der Artikel enthält auch Codebeispiele.

CREATE TABLE AS SELECT

Die Anweisung CREATE TABLE AS SELECT (CTAS) ist eines der wichtigsten verfügbaren T-SQL-Features. CTAS ist ein paralleler Vorgang, bei dem eine neue Tabelle anhand der Ausgabe einer SELECT-Anweisung erstellt wird. CTAS ist die einfachste und schnellste Methode zum Erstellen und Einfügen von Daten in eine Tabelle mit einem einzigen Befehl.

SELECT...INTO im Vergleich zu CTAS

CTAS ist eine stärker anpassbare Version der SELECT...INTO-Anweisung.

Nachstehend sehen Sie ein Beispiel für eine einfache SELECT...INTO-Anweisung:

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

SELECT...INTO lässt jedoch nicht zu, dass Sie im Rahmen des Vorgangs die Verteilungsmethode oder den Indextyp ändern. Sie erstellen [dbo].[FactInternetSales_new] mithilfe des Standardverteilungstyps als ROUND_ROBIN und die Standardtabellenstruktur als GRUPPIERTER COLUMNSTORE-INDEX.

Mit CTAS können Sie andererseits sowohl die Verteilung der Tabellendaten als auch den Tabellenstrukturtyp angeben. So konvertieren Sie das vorherige Beispiel in CTAS:

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

Kopieren einer Tabelle mithilfe von CTAS

Einer der häufigsten Verwendungszwecke für CTAS ist vermutlich das Kopieren einer Tabelle, um den DDL-Code ändern zu können. Angenommen, Sie haben die Tabelle ursprünglich als ROUND_ROBIN erstellt und möchten sie nun in eine Tabelle ändern, die über eine Spalte verteilt ist. In diesem Fall könnten Sie mit CTAS die Verteilungsspalte ändern. Außerdem könnten Sie CTAS zum Ändern der Partitionierung, der Indizierung oder von Spaltentypen verwenden.

Nehmen wir an, dass Sie diese Tabelle durch Festlegen von HEAP und mithilfe des Standardverteilungstyps ROUND_ROBIN erstellt haben.

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

Nun möchten Sie eine neue Kopie dieser Tabelle mit Clustered Columnstore Index erstellen, sodass Sie die Leistung von gruppierten Columnstore-Tabellen nutzen können. Außerdem soll diese Tabelle nach ProductKey verteilt werden, da Sie Verknüpfungen für diese Spalte erwarten und Datenverschiebungen während der Verknüpfung nach ProductKey vermeiden möchten. Schließlich möchten Sie auch eine Partitionierung für OrderDateKey hinzufügen, sodass Sie schnell alte Daten löschen können, indem Sie alte Partitionen verwerfen. Im Folgenden finden Sie die CTAS-Anweisung, mit der Sie die alte Tabelle in eine neue Tabelle kopieren.

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;

Schließlich können Sie die Tabellen umbenennen, um zur neuen Tabelle zu wechseln und die alte Tabelle zu verwerfen.

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

DROP TABLE FactInternetSales_old;

Explizites Angeben des Datentyps und der NULL-Zulässigkeit der Ausgabe

Beim Migrieren von Code stoßen Sie unter Umständen auf diese Art von Codiermuster:

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;

Sie denken vielleicht, dass Sie diesen Code zu CTAS migrieren sollten, und damit hätten Sie auch recht. Hierbei liegt jedoch ein verstecktes Problem vor.

Der folgende Code führt nicht zum gleichen Ergebnis:

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;

Beachten Sie, dass die Spalte „result“ (Ergebnis) die Werte des Datentyps und der NULL-Zulässigkeit des Ausdrucks übernimmt. Das Übertragen des Datentyps kann zu geringfügigen Abweichungen bei den Werten führen, wenn nicht darauf geachtet wird.

Testen Sie dieses Beispiel:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

Der für „result“ gespeicherte Wert unterscheidet sich. Da der in der Spalte „result“ beibehaltene Wert in anderen Ausdrücken verwendet wird, wird der Fehler noch signifikanter.

Dies ist für Datenmigrationen wichtig. Auch wenn die zweite Abfrage natürlich genauer ist, liegt ein Problem vor. Die Daten würden sich gegenüber dem Quellsystem unterscheiden, und dies stellt die Integrität der Migration infrage. Dies ist einer der seltenen Fällen, in denen die „falsche“ Antwort tatsächlich die richtige ist!

Der Grund für diese Ungleichheit zwischen den beiden Ergebnissen ist die implizite Typumwandlung. Im ersten Beispiel definiert die Tabelle die Spaltendefinition. Beim Einfügen der Zeile tritt eine implizite Typumwandlung auf. Im zweiten Beispiel ist keine implizite Typumwandlung vorhanden, da der Ausdruck den Datentyp der Spalte definiert.

Beachten Sie auch, dass die Spalte im zweiten Beispiel so konfiguriert wurde, dass NULL-Werte zulässig sind. Im ersten Beispiel ist dies nicht der Fall. Als die Tabelle im ersten Beispiel erstellt wurde, wurde die NULL-Zulässigkeit der Spalte explizit definiert. Im zweiten Beispiel wurde dies dem Ausdruck überlassen, was standardmäßig zu einer NULL-Definition führen würde.

Zum Lösen dieser Probleme müssen Sie die Typumwandlung und NULL-Zulässigkeit im SELECT-Teil der CTAS-Anweisung explizit festlegen. Sie können diese Eigenschaften nicht in CREATE TABLE festlegen. Das Korrigieren des Codes wird im folgenden Beispiel veranschaulicht:

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

Beachten Sie Folgendes:

• Sie können CAST oder CONVERT verwenden.
• Verwenden Sie zum Erzwingen der NULL-Zulässigkeit ISNULL, nicht COALESCE. Beachten Sie den folgenden Hinweis.
• ISNULL ist die äußerste Funktion.
• Der zweite Teil von ISNULL ist die Konstante 0.

Das Sicherstellen der Integrität Ihrer Berechnungen ist auch für den Tabellenpartitionswechsel wichtig. Stellen Sie sich vor, Sie haben die folgende Tabelle als Faktentabelle definiert:

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

Das amount-Feld ist aber ein berechneter Ausdruck. Es ist nicht Teil der Quelldaten.

Zum Erstellen des partitionierten Datasets können Sie beispielsweise folgenden Code verwenden:

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

Die Abfrage würde problemlos ausgeführt werden. Das Problem entsteht, wenn Sie versuchen, den Partitionswechsel durchzuführen. Die Tabellendefinitionen stimmen nicht überein. Damit die Tabellendefinitionen übereinstimmen, ändern Sie die CTAS-Anweisung, und fügen Sie eine ISNULL-Funktion hinzu, damit das NULL-Zulässigkeitsattribut der Spalte beibehalten wird.

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

Sie sehen also, dass die Typkonsistenz und die Pflege der Eigenschaften für die NULL-Zulässigkeit für CTAS eine bewährte Methode für das Engineering sind. Es ist hilfreich, die Integrität in Ihren Berechnungen zu wahren. Außerdem wird so sichergestellt, dass der Partitionswechsel möglich ist.

CTAS ist eine der wichtigsten Anweisungen in Synapse SQL. Machen Sie sich damit eingehend vertraut. Weitere Informationen finden Sie in der CTAS-Dokumentation.

Nächste Schritte

Weitere Hinweise zur Entwicklung finden Sie in der Entwicklungsübersicht.