CREATE TABLE AS SELECT (CTAS)

2025-04-12

В этой статье описывается применение инструкции T-SQL CREATE TABLE AS SELECT (CTAS) в выделенном пуле SQL (ранее называлось хранилищем данных SQL) для разработки решений. Здесь также приведены примеры кодов.

CREATE TABLE AS SELECT

Инструкция CREATE TABLE AS SELECT (CTAS) — одна из самых важных функций T-SQL. CTAS — это распараллеленная операция, которая создает новую таблицу на основе выходных данных инструкции SELECT. CTAS — это самый простой и быстрый способ создания и вставки данных в таблицу с помощью одной команды.

Сравнение операторов SELECT...INTO и CTAS

CTAS — это версия инструкции SELECT...INTO с более широкими возможностями настройки.

Ниже показан пример простой инструкции SELECT...INTO.

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

SELECT...INTO не позволяет изменять метод распределения или тип индекса как часть операции. Вы создаёте [dbo].[FactInternetSales_new] с использованием типа распределения по умолчанию ROUND_ROBIN и стандартной табличной структуры CLUSTERED COLUMNSTORE INDEX.

С другой стороны, с 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 
);

Now you want to create a new copy of this table, with a Clustered Columnstore Index, so you can take advantage of the performance of Clustered Columnstore tables. Вы также хотите распределить эту таблицу по столбцу ProductKey, так как к этому столбцу будет применяться операция соединения и вы хотите избежать перемещения данных при соединении по столбцу ProductKey. Наконец, вы также хотите добавить секционирование по столбцу OrderDateKey, чтобы быстро удалять старые данные путем удаления старых секций. Here's the CTAS statement, which copies your old table into a new table.

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;

Explicitly state data type and nullability of output

При переносе кода вы можете встретить следующую структуру кода:

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;

Notice that the column "result" carries forward the data type and nullability values of the expression. Если проявить осторожность, перенос типа данных может привести к незначительным расхождениям в значениях.

Ознакомьтесь с этим примером.

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

The value stored for result is different. Так как сохраненное значение в столбце результатов используется в других выражениях, ошибка становится еще более значительной.

Снимок экрана результатов CTAS

Это важно для переноса данных. Несмотря на то, что второй запрос является более точным, здесь есть проблема. Данные будут отличаться по сравнению с исходной системой, и это приведет к вопросам целостности при миграции. Это один из тех редких случаев, когда "неправильный" ответ на самом деле является правильным!

The reason we see a disparity between the two results is due to implicit type casting. В первом примере в таблице приводится определение столбца. При вставке строки происходит неявное преобразование типов. Во втором примере неявное преобразование типов отсутствует, так как выражение определяет тип данных столбца.

Notice also that the column in the second example has been defined as a NULLable column, whereas in the first example it hasn't. При создании таблицы в первом примере была явно указана допустимость значений NULL для столбца. Во втором примере это было оставлено на усмотрение выражения, и по умолчанию это бы означало определение NULL.

To resolve these issues, you must explicitly set the type conversion and nullability in the SELECT portion of the CTAS statement. Эти свойства нельзя задать в инструкции 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

Обратите внимание на следующее:

You can use CAST or CONVERT.
Use ISNULL, not COALESCE, to force NULLability. Ознакомьтесь со следующим примечанием.
ISNULL — это самая внешняя функция.
Вторая часть функции ISNULL является константой, 0.

Примечание.

For the nullability to be correctly set, it's vital to use ISNULL and not COALESCE. COALESCE не является детерминированной функцией, поэтому результат выражения всегда может принимать значение NULL. ISNULL работает иначе. Это детерминировано. Therefore, when the second part of the ISNULL function is a constant or a literal, the resulting value will be 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
                    )
                )
);

Однако поле суммы является вычисляемым выражением. Он не является частью исходных данных.

Для создания секционированного набора данных может потребоваться использовать следующий код.

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

The query would run perfectly well. Проблема возникает при попытке выполнить переключение секций. Определения таблиц не совпадают. Чтобы определения таблиц совпадали, измените 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');

You can see that type consistency and maintaining nullability properties on a CTAS is an engineering best practice. Это помогает поддерживать целостность вычислений и обеспечивает возможность переключения секций.

CTAS — это одна из самых важных инструкций в Synapse SQL. Убедитесь, что вы его тщательно поняли. Дополнительные сведения см. в документации CTAS.

Для получения дополнительных советов по разработке см. обзор разработки.

Поделиться через

CREATE TABLE AS SELECT (CTAS)