CREATE TABLE AS SELECT

發行項
02/23/2024

適用於：Azure Synapse Analytics Analytics Platform System (PDW)

CREATE TABLE AS SELECT (CTAS) 是最重要的 T-SQL 功能之一。該作業與根據 SELECT 陳述式輸出來建立新資料表的作業完全平行。 CTAS 是建立資料表複本最快、最簡單的方法。

例如，使用 CTAS 可執行以下作業：

重新建立具有不同雜湊散發資料行的資料表。
重新建立如同複寫的資料表。
只在資料表的部分資料行上，建立資料行存放區索引。
查詢或匯入的外部資料。

注意

因為 CTAS 擴充了原本的資料表建立功能，所以本主題不再重複討論 CREATE TABLE 主題。我們將重點放在描述 CTAS 和 CREATE TABLE 陳述式之間的差異。如需 CREATE TABLE 詳細資訊，請參閱 CREATE TABLE (Azure Synapse Analytics) 陳述式。

Azure Synapse Analytics 的無伺服器 SQL 集區不支援此語法。
Microsoft Fabric 中的倉儲支援 CTAS。

Transact-SQL 語法慣例

語法

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 
    }

引數

如需詳細資訊，請參閱 CREATE TABLE 中的引數小節。

資料行選項

column_name [ ,...n ]
數據行名稱不允許 CREATE TABLE 中提及的數據行選項。您反而應該為新資料表提供一個由一或多個資料行名稱構成的選擇性清單。新數據表中的數據行會使用您指定的名稱。當您指定資料行名稱時，資料行清單中的資料行數目必須與選取結果中的資料行數目相符。如果您未指定任何資料行名稱，新目標資料表會使用選取陳述式結果中的資料行名稱。

您無法指定任何其他資料行選項，例如資料類型、定序或可 Null 性。這些屬性每個都是從 SELECT 陳述式的結果衍生而來的。不過，您可以使用 SELECT 陳述式來變更屬性。如需範例，請參閱使用 CTAS 來變更資料行的屬性。

資料表散發選項

如需詳細資訊以及了解如何選擇最佳散發資料行，請參閱 CREATE TABLE 中的資料表散發選項小節。如需根據實際使用量或範例查詢為資料表選擇散發的建議，請參閱 Azure Synapse SQL 中的散發建議程式。

DISTRIBUTION = HASH （distribution_column_name） |ROUND_ROBIN |REPLICATE CTAS 語句需要散發選項，而且沒有預設值。這與 CREATE TABLE 不同，預設值為。

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) 根據最多八個資料行的雜湊值散發資料列，可以更平均地分佈基底資料表資料，減少隨著時間而產生的資料扭曲並改善查詢效能。

注意

若要啟用功能，請使用此命令將資料庫的相容性層級變更為 50。如需設定資料庫相容性層級的詳細資訊，請參閱 ALTER DATABASE SCOPED CONFIGURATION。例如：ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
若要停用多重資料行散發 (MCD) 功能，請執行此命令將資料庫的相容性層級變更為 AUTO。例如：ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; 現有的 MCD 資料表會保持不變，但變得無法讀取。 MCD 資料表的查詢會傳回此錯誤：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.
- 若要重新取得 MCD 資料表的存取權，請再次啟用此功能。
- 若要將資料載入 MCD 資料表，請使用 CTAS 陳述式，而且資料來源必須是 Synapse SQL 資料表。
- 不支援 MCD HEAP 目標資料表上的 CTAS。請改用 INSERT SELECT 作為因應措施，以將資料載入 MCD HEAP 資料表。
目前在 SSMS 版本 19 以上支援使用 SSMS 產生指令碼來建立 MCD 資料表。

如需詳細資訊以及了解如何選擇最佳散發資料行，請參閱 CREATE TABLE 中的資料表散發選項小節。

如需根據工作負載使用的最佳散發建議，請參閱 Synapse SQL 散發建議程式（預覽版）。

資料表資料分割選項

CTAS 語句預設會建立非分割數據表，即使源數據表已分割也一樣。若要使用 CTAS 陳述式來建立資料分割資料表，您必須指定資料分割選項。

如需詳細資訊，請參閱 CREATE TABLE 中的資料表資料分割選項小節。

SELECT 陳述式

SELECT 語句是 CTAS 與 CREATE TABLE 之間的基本差異。

`WITH`common_table_expression

指定稱為通用資料表運算式 (CTE) 的暫存具名結果集。如需詳細資訊，請參閱 WITH common_table_expression (Transact-SQL)。

`SELECT`select_criteria

將 SELECT 陳述式產生的結果填入新資料表。 select_criteria 是 SELECT 陳述式的主體，可決定要複製到新資料表的資料。如需 SELECT 陳述式的相關資訊，請參閱 SELECT (Transact-SQL)。

查詢提示

使用者可以將 MAXDOP 設定為整數值，以控制平行處理原則的最大程度。當 MAXDOP 設為 1 時，會由單一執行緒執行查詢。

權限

CTAS 需要 select_criteria 中所參考任何物件的 SELECT 權限。

如需資料表的建立權限，請參閱 CREATE TABLE 中的權限。

備註

如需詳細資訊，請參閱 CREATE TABLE 中的一般備註。

限制事項

已排序的叢集資料行存放區索引可以建立在 Azure Synapse Analytics 支援的任何資料類型的資料行上，但不包括字串資料行。

SET ROWCOUNT (Transact-SQL) 對 CTAS 沒有作用。若要達到類似的行為，請使用 TOP (Transact-SQL)。

如需詳細資訊，請參閱 CREATE TABLE 中的限制事項。

鎖定行為

如需詳細資訊，請參閱 CREATE TABLE 中的鎖定行為。

效能

至於雜湊散發的資料表，您可以使用 CTAS 來選擇不同的散發資料行，讓聯結和彙總達到更佳的效能。如果您選擇不同的散發資料行不是您的目標，如果您指定相同的散發數據行，您將擁有最佳的 CTAS 效能，因為這可避免轉散發數據列。

如果您使用 CTAS 來建立資料表和效能不是因素，您可以指定 ROUND_ROBIN 以避免必須決定散發數據行。

若要避免在後續查詢中移動資料，可以指定 REPLICATE，但代價是須要增加儲存體，才能載入每一個計算節點的資料表完整複本。

資料表複製範例

A. 使用 CTAS 複製資料表

適用於：Azure Synapse Analytics 和 Analytics Platform System (PDW)

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

現在您想要建立具有叢集資料行存放區索引的此資料表新複本，以便您可以善用叢集資料行存放區資料表的效能。您也想要在此數據表上散發此數據表 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;

資料行選項範例

B. 使用 CTAS 來變更資料行屬性

適用於：Azure Synapse Analytics 和 Analytics Platform System (PDW)

這個範例會使用 CTAS 來為 DimCustomer2 資料表的數個資料行變更資料類型、可 Null 性和定序。

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

在最後的步驟中，您可以使用 RENAME (Transact-SQL) 來切換資料表名稱。這樣會將 DimCustomer2 變成新的資料表。

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

DROP TABLE DimCustomer2_old;

資料表散發範例

C. 使用 CTAS 來變更資料表的散發方法

適用於：Azure Synapse Analytics 和 Analytics Platform System (PDW)

這個簡易範例會示範如何變更資料表的散發方法。為了示範整個操作流程，它會將雜湊散發資料表變更為循環配置資源資料表，然後再將循環配置資源資料表變更回雜湊散發資料表。最後的資料表將與原始資料表相符。

在大部分情況下，您不需要將哈希分散式數據表變更為迴圈配置資源數據表。更多的情況是，您可能需要將循環配置資源資料表變更為雜湊散發資料表。例如，您可能一開始將新的資料表載入為循環配置資源資料表，但之後將其移至雜湊散發資料表以獲取更佳的聯結效能。

這個範例會使用 AdventureWorksDW 範例資料庫。若要載入 Azure Synapse Analytics 版本，請參閱快速入門：使用 Azure 入口網站建立和查詢 Azure Synapse Analytics 中的專用 SQL 集區 (先前稱為 SWL DW)。

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

接下來，將它變更回雜湊散發資料表。

-- 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. 使用 CTAS 將資料表轉換成複寫資料表

適用於：Azure Synapse Analytics 和 Analytics Platform System (PDW)

此範例適用於將循環配置資源資料表或雜湊散發資料表轉換為複寫資料表。這個特殊範例會針對之前的散發類型變更方法，做更進一步的應用。因為 DimSalesTerritory 是一個維度，而且可能是小型的資料表，因此可以選擇將資料表重新建立為複寫資料表，這樣在聯結至其他資料表時，就能避免移動資料。

-- 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. 使用 CTAS 來建立資料行較少的資料表

適用於：Azure Synapse Analytics 和 Analytics Platform System (PDW)

下列範例會建立名為 myTable (c, ln) 的循環配置資源散發資料表。新的資料表只有兩個資料行。它會使用 SELECT 陳述式中的資料行別名來作為資料行的名稱。

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

查詢提示範例

F. 查詢提示與 CREATE TABLE AS SELECT (CTAS) 搭配使用

適用於：Azure Synapse Analytics 和 Analytics Platform System (PDW)

此查詢示會示範查詢聯結提示與 CTAS 陳述式搭配使用的基本語法。提交查詢後，Azure Synapse Analytics 會在為每一個散發產生查詢計劃時，套用雜湊聯結策略。如需有關雜湊聯結查詢提示的詳細資訊，請參閱 OPTION 子句 (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 );

外部資料表範例

G. 使用 CTAS 來從 Azure Blob 儲存體匯入資料

適用於：Azure Synapse Analytics 和 Analytics Platform System (PDW)

若要從外部數據表匯入數據，請使用 CREATE TABLE AS SELECT 從外部資料表選取。從外部資料表選取資料來匯入 Azure Synapse Analytics 時所用的語法，與從一般資料表中選取資料時所使用語法相同。

下列範例會針對 Azure Blob 儲存體帳戶中的資料，定義一個外部資料表。接著它會使用 CREATE TABLE AS SELECT，從外部資料表進行選取。這樣會從 Azure Blob 儲存體文字分隔的檔案匯入資料，然後將資料儲存至新的 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. 使用 CTAS 來從外部資料表匯入 Hadoop 資料

適用於：Analytics Platform System (PDW)

若要從外部資料表匯入資料，只要使用 CREATE TABLE AS SELECT 來從外部資料表進行選取即可。從外部資料表選取資料來匯入 Analytics Platform System (PDW) 時所用的語法，與從一般資料表中選取資料時所使用語法相同。

下列範例會在 Hadoop 叢集上定義一個外部資料表。接著它會使用 CREATE TABLE AS SELECT，從外部資料表進行選取。這樣會從 Hadoop 文字分隔的檔案匯入資料，然後將資料儲存至新的 Analytics Platform System (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  
;

使用 CTAS 來取代 SQL Server 程式碼的範例

使用 CTAS 來解決一些不支援的功能。除了可以在資料倉儲上執行自己的程式碼之外，重新撰寫現有程式碼來使用 CTAS，通常還能改善效能。這是 CTAS 完全平行化設計的結果。

注意

試著「優先考慮 CTAS」。如果您認為使用 CTAS 可以解決問題，通常這是最好的方法，即使您會因此而撰寫更多的資料。

I. 使用 CTAS 而不使用 SELECT..INTO

適用於：Azure Synapse Analytics 和 Analytics Platform System (PDW)

SQL Server 程式碼通常會使用 SELECT...INTO 來將 SELECT 陳述式的結果填入資料表。這是一個 SQL Server SELECT..INTO 陳述式的範例。

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

Azure Synapse Analytics 和平行處理數據倉儲不支援此語法。這個範例會示範如何將以前的 SELECT..INTO 陳述式，重新撰寫為 CTAS 陳述式。您可以選擇 CTAS 語法中所描述的任何 DISTRIBUTION 選項。這個範例會使用 ROUND_ROBIN 散發方法。

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

J. 使用 CTAS 來簡化合併陳述式

適用於：Azure Synapse Analytics 和 Analytics Platform System (PDW)

使用 CTAS 可以取代至少部分取代合併陳述式。您可以將 INSERT 和 UPDATE 合併成一個陳述式。任何刪除的記錄都應該在第二個陳述式中關閉。

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. 明確陳述資料類型和輸出可為 null

適用於：Azure Synapse Analytics 和 Analytics Platform System (PDW)

將 SQL Server 程式碼移轉至 Azure Synapse Analytics 時，您可能碰到這種類型的程式碼模式：

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
;

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

A screenshot from SQL Server Management Studio (SSMS) of the CREATE TABLE AS SELECT results.

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

之所以會在這兩個結果之間看到差異，原因與隱含類型轉換有關。在第一個範例中，資料表定義了資料行。插入資料列時，就會發生隱含類型轉換。在第二個範例中，沒有隱含類型轉換，因為表達式會定義數據行的數據類型。另請注意，第二個範例中的數據行已定義為 NULLable 數據行，而在第一個範例中則沒有。在第一個範例中建立資料表時，會明確定義料資料行的可 Null 性。在第二個 NULL 範例中，它留給表達式，根據預設，這會導致定義。

若要解決這些問題，您必須在 CTAS 陳述式的 SELECT 部分，明確設定型別轉換和可 Null 性。您無法在建立資料表元件中設定這些屬性。

此範例會示範如何修正程式碼：

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 是用來強制可 Null 性，而不是 COALESCE。
ISNULL 是最外層的函數。
ISNULL 的第二個部分是常數 0。

注意

若要正確地設定可 Null 性，請務必使用 ISNULL，而不要使用 COALESCE。 COALESCE 不是一個確定性函數，因此運算式的結果將永遠是可為 Null。 ISNULL 不一樣。它是具確定性的。因此，當 ISNULL 函數的第個二部分是一個常數或常值時，則產生的值將會是非 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
                    )
                )
)
;

不過，值欄位是計算運算式，不是源數據的一部分。

若要建立分割資料集，請考慮下列範例：

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。

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 性屬性，是正確的操縱最佳作法。這有助於維護計算的完整性，也能夠確定資料分割切換的可行性。

L. 使用 MAXDOP 1 建立已排序的叢集資料行存放區索引

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

下一步

適用於：Microsoft Fabric 中的 Warehouse

例如，在 Microsoft Fabric 的倉儲中使用 CTAS 來：

使用源數據表的一些數據行建立數據表複本。
建立數據表，這是聯結其他數據表的查詢結果。

如需在 Microsoft Fabric 中使用倉儲 CTAS 的詳細資訊，請參閱使用 Transact-SQL 將數據內嵌至您的倉儲。

注意

因為 CTAS 擴充了原本的資料表建立功能，所以本主題不再重複討論 CREATE TABLE 主題。我們將重點放在描述 CTAS 和 CREATE TABLE 陳述式之間的差異。如需 CREATE TABLE 詳細數據，請參閱 CREATE TABLE 語句。

Transact-SQL 語法慣例

語法

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

<select_statement> ::=  
    SELECT select_criteria

引數

如需詳細資訊，請參閱 CREATE TABLE for Microsoft Fabric 中的自變數。

資料行選項

您無法指定任何其他資料行選項，例如資料類型、定序或可 Null 性。這些屬性每個都是從 SELECT 陳述式的結果衍生而來的。不過，您可以使用 SELECT 陳述式來變更屬性。

SELECT 陳述式

SELECT 語句是 CTAS 與 CREATE TABLE 之間的基本差異。

`SELECT`select_criteria

注意

在 Microsoft Fabric 中，不允許在 CTAS 中使用變數。

權限

CTAS 需要 select_criteria 中所參考任何物件的 SELECT 權限。

如需資料表的建立權限，請參閱 CREATE TABLE 中的權限。

備註

如需詳細資訊，請參閱 CREATE TABLE 中的一般備註。

限制事項

SET ROWCOUNT (Transact-SQL) 對 CTAS 沒有作用。若要達到類似的行為，請使用 TOP (Transact-SQL)。

如需詳細資訊，請參閱 CREATE TABLE 中的限制事項。

鎖定行為

如需詳細資訊，請參閱 CREATE TABLE 中的鎖定行為。

資料表複製範例

如需在 Microsoft Fabric 中使用倉儲 CTAS 的詳細資訊，請參閱使用 Transact-SQL 將數據內嵌至您的倉儲。

A. 使用 CTAS 來變更資料行屬性

此範例會使用 CTAS 來變更數據表中數個數據行的 DimCustomer2 數據類型和 Null 性。

-- 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. 使用 CTAS 來建立資料行較少的資料表

下列範例會建立名為的 myTable (c, ln)數據表。新的資料表只有兩個資料行。它會使用 SELECT 陳述式中的資料行別名來作為資料行的名稱。

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

C. 使用 CTAS 而不使用 SELECT..INTO

SQL Server 程式碼通常會使用 SELECT...INTO 來將 SELECT 陳述式的結果填入資料表。這是一個 SQL Server SELECT..INTO 陳述式的範例。

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

這個範例會示範如何將以前的 SELECT..INTO 陳述式，重新撰寫為 CTAS 陳述式。

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

D. 使用 CTAS 來簡化合併陳述式

使用 CTAS 可以取代至少部分取代合併陳述式。您可以將 INSERT 和 UPDATE 合併成一個陳述式。任何刪除的記錄都應該在第二個陳述式中關閉。

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

CREATE TABLE AS SELECT

語法

引數

資料行選項

資料表散發選項

資料表資料分割選項

SELECT 陳述式

WITHcommon_table_expression

SELECTselect_criteria

查詢提示

權限

備註

限制事項

鎖定行為

效能

資料表複製範例

A. 使用 CTAS 複製資料表

資料行選項範例

B. 使用 CTAS 來變更資料行屬性

資料表散發範例

C. 使用 CTAS 來變更資料表的散發方法

D. 使用 CTAS 將資料表轉換成複寫資料表

E. 使用 CTAS 來建立資料行較少的資料表

查詢提示範例

F. 查詢提示與 CREATE TABLE AS SELECT (CTAS) 搭配使用

外部資料表範例

G. 使用 CTAS 來從 Azure Blob 儲存體匯入資料

H. 使用 CTAS 來從外部資料表匯入 Hadoop 資料

使用 CTAS 來取代 SQL Server 程式碼的範例

I. 使用 CTAS 而不使用 SELECT..INTO

J. 使用 CTAS 來簡化合併陳述式

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

L. 使用 MAXDOP 1 建立已排序的叢集資料行存放區索引

下一步

語法

引數

資料行選項

SELECT 陳述式

SELECTselect_criteria

權限

備註

限制事項

鎖定行為

資料表複製範例

A. 使用 CTAS 來變更資料行屬性

B. 使用 CTAS 來建立資料行較少的資料表

C. 使用 CTAS 而不使用 SELECT..INTO

D. 使用 CTAS 來簡化合併陳述式

下一步

意見反應

意見反應

其他資源

`WITH`common_table_expression

`SELECT`select_criteria

`SELECT`select_criteria