CREATE TABLE AS SELECT (CTAS)

發行項
06/14/2024

本文說明專用 SQL 集區中 CREATE TABLE AS SELECT （CTAS） T-SQL 語句，用於開發解決方案。此文章也提供程式碼範例。

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT （CTAS）語句是其中一項最重要的 T-SQL 功能。 CTAS 是一種平行作業，可根據 SELECT 語句的輸出建立新的數據表。 CTAS 是使用單一命令在數據表中建立和插入數據的最簡單且最快的方式。

選擇。。。INTO 與 CTAS

CTAS 是更可自定義的 SELECT 版本...INTO 語句。

以下是簡單 SELECT... 的範例到：

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

SELECT...INTO 不允許在作業期間變更散發方式或索引類型。您可以使用ROUND_ROBIN的默認散發類型，以及 CLUSTERED COLUMNSTORE INDEX 的預設數據表結構來建立 [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 來變更資料分割、編製索引或數據行類型。

假設您已藉由指定 HEAP 並使用的預設散發類型 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 結果的螢幕快照

對於數據遷移而言，這很重要。雖然第二個查詢可以說更準確，但還是有問題。與來源系統相比，數據會有所不同，這會導致移轉中的完整性問題。這是罕見案例之一，也就是「錯誤」的答案實際上是正確的答案！

我們看到這兩個結果之間差異的原因是隱含類型轉換。在第一個範例中，資料表定義了資料行。插入數據列時，會發生隱含類型轉換。在第二個範例中，沒有隱含類型轉換，因為表達式會定義數據行的數據類型。

另請注意，第二個範例中的數據行已定義為 NULLable 數據行，而在第一個範例中則沒有。在第一個範例中建立數據表時，會明確定義數據行可為 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來強制NLability。請參閱下列附註。
ISNULL 是最外層的函數。
ISNULL 的第二個部分是常數 0。

注意

若要正確設定 Null 功能，請務必使用 ISNULL，而不是 COALESCE。 COALESCE 不是決定性函數，因此表達式的結果一律為 NULLable。 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)