CREATE TABLE AS SELECT (CTAS)

發行項
06/01/2023

本文說明專用 SQL 集區 (先前稱為 SQL DW) 中用於開發解決方案的 CREATE TABLE AS SELECT (CTAS) (CTAS) T-SQL 陳述式。此文章也提供程式碼範例。

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT陳述式是最重要的可用 T-SQL 功能之一。 CTAS 是根據 SELECT 陳述式的輸出來建立新資料表的平行作業。若要使用單一命令來建立資料表並於其中插入資料，CTAS 是最簡單且最快速的方式。

SELECT...INTO 與 CTAS 的比較

CTAS 是自訂方式更簡單的 SELECT...INTO 陳述式版本。

以下舉例說明簡單的 SELECT..INTO：

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

SELECT...INTO 不允許在作業期間變更散發方式或索引類型。您可以使用 ROUND_ROBIN 的預設散發類型和叢集資料行存放區索引的預設資料表結構來建立 [dbo].[FactInternetSales_new]。

另一方面，您可以使用 CTAS 來指定資料表資料的散發，以及資料表結構類型。若要將前一個範例轉換成 CTAS：

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

注意

如果您只是想要變更 CTAS 作業中的索引，且來源資料表會以雜湊散發，則請保有相同的散發資料行和資料類型。這樣可避免在作業期間有跨越分散資料移動，所以會有較佳效能。

使用 CTAS 複製資料表

或許 CTAS 最常見的用途之一，就是建立資料表複本以便變更 DDL。假設您原本將資料表建立為 ROUND_ROBIN，而現在想要將其變更為散發在資料行上的資料表。 CTAS 便是用來變更散發資料行的方式。您也可以使用 CTAS 來變更資料分割、索引或資料行的類型。

假設您指定 HEA 並使用預設的散發類型 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 
);

現在您想要針對此資料表建立具有 Clustered Columnstore Index 的新資料表複本，以便能夠利用叢集資料行存放區資料表的效能。您也想要在 ProductKey 上散發此資料表，因為您預期這個資料行會進行聯結，並想要在 ProductKey 進行聯結的期間避免資料移動。最後您也想要在 OrderDateKey 上加入資料分割，以便能透過卸除舊資料分割來快速刪除舊資料。以下是會將舊資料表複製到新資料表的 CTAS 陳述式。

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;

最後您也可以重新命名資料表，以便以新資料表來交換，然後再卸除舊的資料表。

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

DROP TABLE FactInternetSales_old;

明確陳述資料類型和輸出可為 null

移轉程式碼時，您可能會發現自己正在跨這種類型的程式碼撰寫模式執行：

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;

您可能會認為您應該將此程式碼遷移至 CTAS，這麼想並沒有錯。不過，這其中隱藏了一個問題。

下列程式碼不會產生相同的結果：

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;

請注意，資料行 "result" 會帶有運算式的資料類型和可 Null 性。如果不仔細進行，帶入資料類型可能會導致值產生細微差異。

請嘗試此範例：

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

為結果而儲存的值不相同。當結果資料行中的持續值用在其他運算式時，錯誤會變得更加明顯。

CTAS 結果的螢幕擷取畫面

這對資料移轉相當重要。儘管第二個查詢無疑更為準確，但還是有一個問題。與來源系統相比之下，資料會不同，因而導致資料在移轉時出現完整性問題。這是罕見案例之一，也就是「錯誤」的答案實際上是正確的答案！

之所以會在這兩個結果之間看到差異，原因與隱含類型轉換有關。在第一個範例中，資料表定義了資料行。插入資料列時，就會發生隱含類型轉換。在第二個範例中，沒有任何隱含類型轉換，因為運算式會定義資料行的資料類型。

另請注意，第二個範例中的資料行已定義成一個可為 Null 的資料行，而在第一個範例中並未這樣定義。在第一個範例中建立資料表時，會明確定義料資料行的可 Null 性。在第二個範例中，則交給運算式決定。根據預設，這樣會產生一個 NULL 定義。

若要解決這些問題，您必須在 CTAS 陳述式的 SELECT 部分中明確設定類型轉換和可 NULL 性。您無法在 'CREATE TABLE' 中設定這些屬性。下列範例會示範如何修正程式碼：

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

請注意：

您可以使用 CAST 或 CONVERT。
請使用 ISNULL (而非 COALESCE) 來強制實施可 NULL 性。請參閱下列附註。
ISNULL 是最外層的函數。
ISNULL 的第二個部分是常數 0。

注意

若要正確地設定可 NULL 性，必須使用 ISNULL 而不是 COALESCE。 COALESCE 不是具決定性的函數，因此運算式的結果一律可為 Null。但 ISNULL 不同。其具有確定性。因此當 ISNULL 函數的第二個部分是常數或常值，結果值將會是 NOT NULL。

確保計算的完整性對於資料表分割切換而言也很重要。想像您將這個資料表定義為事實資料表：

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

不過，amount 欄位是計算運算式。其不是來源資料的一部分。

若要建立分割的資料集，建議您使用下列程式碼：

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

這個查詢可正確執行。當您嘗試執行分割切換時，便會發生問題。資料表定義不相符。若要讓資料表定義相符，請修改 CTAS 來新增 ISNULL 函式，以保留資料行的可 NULL 性屬性。

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

您會發現 CTAS 上的類型一致性以及維護可 Null 性屬性，是正確的操縱最佳做法。這有助於維護計算的完整性，也能夠確定分割切換的可行性。

CTAS 是 Synapse SQL 中最重要的其中一個陳述式。請確定您已徹底了解。請參閱 CTAS 文件。

後續步驟

如需更多開發祕訣，請參閱開發概觀。

CREATE TABLE AS SELECT (CTAS)