CREATE TABLE AS SELECT (CTAS)

Artikel
06/01/2023

Artikel ini berisi penjelasan dan contoh pernyataan T-SQL CREATE TABLE AS SELECT (CTAS) di kumpulan SQL khusus (sebelumnya SQL DW) untuk mengembangkan solusi. Artikel ini juga menyediakan contoh kode.

CREATE TABLE AS SELECT

Pernyataan CREATE TABLE AS SELECT (CTAS) adalah salah satu fitur T-SQL terpenting yang tersedia. CTAS adalah operasi paralel yang membuat tabel baru berdasarkan output pernyataan PILIH. CTAS adalah cara paling sederhana dan tercepat untuk membuat dan menyisipkan data ke dalam tabel dengan satu perintah.

PILIH... KE DALAM vs. CTAS

CTAS adalah versi pernyataan PILIH...KE DALAM yang lebih dapat disesuaikan.

Berikut ini adalah contoh sederhana PILIH...KE DALAM:

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

Pilih... KE DALAM tidak mengizinkan Anda mengubah metode distribusi atau jenis indeks sebagai bagian dari operasi. Anda membuat [dbo].[FactInternetSales_new] dengan menggunakan jenis distribusi default ROUND_ROBIN, dan struktur tabel default INDEKS PENYIMPANAN KOLOM TERKLUSTER.

Dengan CTAS, di sisi lain, Anda dapat menentukan distribusi data tabel serta jenis struktur tabel. Untuk mengonversi contoh sebelumnya ke CTAS:

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

Catatan

Jika Anda hanya mencoba mengubah indeks dalam operasi CTAS Anda, dan tabel sumber didistribusikan hash, pertahankan kolom distribusi dan jenis data yang sama. Ini menghindari perpindahan data lintas distribusi selama operasi, yang lebih efisien.

Menggunakan CTAS untuk menyalin tabel

Mungkin salah satu penggunaan CTAS yang paling umum adalah membuat salinan tabel untuk mengubah DDL. Katakanlah Anda awalnya membuat tabel Anda sebagai ROUND_ROBIN, dan sekarang ingin mengubahnya menjadi tabel yang didistribusikan pada kolom. CTAS adalah bagaimana Anda akan mengubah kolom distribusi. Anda juga dapat menggunakan CTAS untuk mengubah jenis partisi, pengindeksan, atau kolom.

Katakanlah Anda membuat tabel ini dengan menentukan HEAP dan menggunakan jenis distribusi default 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 
);

Sekarang Anda ingin membuat salinan baru dari tabel ini, dengan Clustered Columnstore Index, sehingga Anda dapat memanfaatkan kinerja tabel Penyimpanan Kolom Terkluster. Anda juga ingin mendistribusikan tabel ini di ProductKey, karena Anda mengantisipasi bergabung di kolom ini dan ingin menghindari perpindahan data selama bergabung di ProductKey. Terakhir, Anda juga ingin menambahkan partisi pada OrderDateKey, sehingga Anda dapat menghapus data lama dengan cepat dengan menghapus partisi lama. Berikut adalah pernyataan CTAS, yang menyalin tabel lama Anda ke dalam tabel baru.

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;

Terakhir, Anda dapat mengganti nama tabel Anda, untuk menukar tabel baru lalu menghapus tabel lama Anda.

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

DROP TABLE FactInternetSales_old;

Secara eksplisit menyatakan jenis data dan output yang dapat diubah ke null

Saat memigrasi kode, Anda mungkin menemukan jenis pola pengodean ini:

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;

Anda mungkin berpikir Anda harus memigrasikan kode ini ke CTAS, dan Anda benar. Namun, ada masalah tersembunyi di sini.

Kode berikut tidak menghasilkan hasil yang sama:

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;

Perhatikan bahwa kolom "hasil" meneruskan jenis data dan nilai yang dapat diubah ke null dari ekspresi. Membawa jenis data ke depan dapat menyebabkan perbedaan nilai yang tipis jika Anda tidak berhati-hati.

Cobalah contoh ini:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

Nilai disimpan untuk hasil berbeda. Karena nilai dipertahankan di kolom hasil digunakan dalam ekspresi lain, kesalahan menjadi lebih signifikan.

Cuplikan layar hasil CTAS

Ini penting untuk migrasi data. Meskipun kueri kedua bisa dibilang lebih akurat, tetap ada masalah. Data akan berbeda dibandingkan dengan sistem sumber, dan itu mengarah pada pertanyaan tentang integritas dalam migrasi. Ini adalah salah satu kasus langka di mana jawaban "salah" sebenarnya adalah yang benar!

Alasan kita melihat perbedaan antara kedua hasil adalah karena pentransmisian jenis implisit. Dalam contoh pertama, tabel menentukan definisi kolom. Saat baris disisipkan, konversi jenis implisit terjadi. Dalam contoh kedua, tidak ada konversi jenis implisit karena ekspresi mendefinisikan jenis data kolom.

Perhatikan juga bahwa kolom dalam contoh kedua telah didefinisikan sebagai kolom dapat diubah ke NULL, sedangkan dalam contoh pertama belum. Ketika tabel dibuat dalam contoh pertama, kolom yang dapat diubah ke null secara eksplisit didefinisikan. Pada contoh kedua, ekspresi dibiarkan, dan secara default akan menghasilkan definisi NULL.

Untuk mengatasi masalah ini, Anda harus secara eksplisit mengatur konversi jenis dan dapat diubah ke null di bagian PILIH dari pernyataan CTAS. Anda tidak dapat mengatur properti ini di 'BUAT TABEL'. Contoh berikut menunjukkan cara memperbaiki kode:

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

Perhatikan hal-hal berikut:

Anda dapat menggunakan CAST atau CONVERT.
Gunakan ISNULL, bukan COALESCE, untuk memaksa kemampuan untuk diubah ke NULL. Lihat catatan berikut.
ISNULL adalah fungsi terluar.
Bagian kedua dari ISNULL adalah konstan, 0.

Catatan

Agar kemampuan untuk diubah ke null diatur dengan benar, sangat penting untuk menggunakan ISNULL dan bukan COALESCE. COALESCE bukan fungsi deterministik, sehingga hasil ekspresi akan selalu yang dapat diubah ke NULL. ISNULL berbeda. Ini deterministik. Oleh karena itu, ketika bagian kedua dari fungsi ISNULL adalah konstanta atau literal, nilai yang dihasilkan TIDAK AKAN NULL.

Memastikan integritas perhitungan Anda juga penting untuk pengalihan partisi tabel. Bayangkan Anda memiliki tabel ini didefinisikan sebagai tabel fakta:

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

Namun, bidang jumlah adalah ekspresi terhitung. Ini bukan bagian dari data sumber.

Untuk membuat set data yang dipartisi, Anda mungkin ingin menggunakan kode berikut:

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

Kueri akan berjalan dengan baik. Masalahnya muncul ketika Anda mencoba untuk melakukan mengalihkan partisi. Definisi tabel tidak cocok. Agar definisi tabel cocok, ubah CTAS untuk menambahkan fungsi ISNULL untuk mempertahankan atribut kolom berkemampuan diubah ke 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');

Anda dapat melihat bahwa konsistensi jenis dan mempertahankan properti kemampuan diubah ke null pada CTAS adalah praktik terbaik teknik. Ini membantu menjaga integritas dalam perhitungan Anda, dan juga memastikan bahwa pengalihan partisi dimungkinkan.

CTAS adalah salah satu pernyataan paling penting dalam Synapse SQL. Pastikan Anda benar-benar memahaminya. Lihat dokumentasi CTAS.

Langkah berikutnya

Untuk tips pengembangan selengkapnya, buka ringkasan pengembangan.

CREATE TABLE AS SELECT (CTAS)