BUAT TABEL DENGAN PERINTAH SELECT (CTAS)

Tip

Microsoft Fabric Data Warehouse adalah gudang relasional skala perusahaan pada fondasi data lake, dengan arsitektur siap masa depan, AI bawaan, dan fitur baru. Jika Anda baru menggunakan pergudangan data, mulailah dengan Fabric Data Warehouse. Beban kerja kumpulan SQL terdedikasi yang ada dapat ditingkatkan ke Fabric untuk mengakses kemampuan baru di seluruh ilmu data, analitik waktu nyata, dan pelaporan.

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

BUAT TABEL DENGAN 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 SELECT. CTAS adalah cara paling sederhana dan tercepat untuk membuat dan menyisipkan data ke dalam tabel dengan satu perintah.

PILIH... INTO vs. CTAS

CTAS adalah versi SELECT...INTO yang lebih dapat disesuaikan.

Berikut ini adalah contoh sederhana dari SELECT...INTO:

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

PILIH... INTO tidak memungkinkan 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 CLUSTERED COLUMNSTORE INDEX.

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 pada operasi CTAS, dan tabel sumber menggunakan distribusi hash, pertahankan kolom distribusi dan jenis data yang sama. Ini menghindari pergerakan data lintas distribusi selama operasi, yang lebih efisien.

Menggunakan CTAS untuk menyalin tabel

Mungkin salah satu kegunaan CTAS yang paling umum adalah membuat salinan tabel untuk mengubah DDL. Katakanlah Anda awalnya membuat tabel sebagai ROUND_ROBIN, dan sekarang ingin mengubahnya menjadi tabel yang didistribusikan pada kolom. CTAS adalah metode untuk 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 tabel ini dengan Clustered Columnstore Index, agar Anda dapat memanfaatkan performa tabel Kolom Berkluster Terkelompok. Anda juga ingin mendistribusikan tabel ini pada ProductKey, karena Anda mengantisipasi gabungan pada kolom ini dan ingin menghindari pergerakan data selama gabungan pada ProductKey. Terakhir, Anda juga ingin menambahkan partisi pada OrderDateKey, sehingga Anda dapat dengan cepat menghapus data lama dengan menghilangkan 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, memasukkan tabel baru Anda, 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 memigrasikan kode, Anda mungkin menemukan Anda menghadapi jenis pola pengkodean 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 akan 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 varians halus dalam nilai jika Anda tidak berhati-hati.

Coba contoh ini:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

Nilai yang disimpan untuk hasil berbeda. Karena nilai yang bertahan dalam 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, ada masalah. Data akan berbeda dibandingkan dengan sistem sumber, dan yang mengarah pada pertanyaan integritas dalam migrasi. Ini adalah salah satu kasus langka di mana jawaban "salah" sebenarnya adalah yang benar!

Alasan kita melihat adanya perbedaan antara kedua hasil disebabkan oleh pencastingan tipe implisit. Dalam contoh pertama, tabel menentukan definisi kolom. Saat baris disisipkan, konversi tipe implisit terjadi. Dalam contoh kedua, tidak ada konversi jenis implisit karena ekspresi menentukan jenis data kolom.

Perhatikan juga bahwa kolom dalam contoh kedua telah didefinisikan sebagai kolom yang dapat bernilai NULL, sedangkan dalam contoh pertama tidak. Ketika tabel dibuat dalam contoh pertama, nullability kolom ditentukan secara eksplisit. Dalam contoh kedua, itu dibiarkan ke ekspresi, dan secara default akan menghasilkan definisi NULL.

Untuk mengatasi masalah ini, Anda harus secara eksplisit mengatur konversi jenis dan nullability di bagian SELECT dari pernyataan CTAS. Anda tidak dapat mengatur properti ini di 'CREATE TABLE'. 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 berikut:

Anda dapat menggunakan CAST atau CONVERT.
Gunakan ISNULL, bukan COALESCE, untuk memaksa NULLability. Lihat catatan berikut.
ISNULL adalah fungsi terluar.
Bagian kedua dari ISNULL adalah konstanta, 0.

Catatan

Agar nullability disetel dengan benar, sangat penting untuk menggunakan ISNULL dan bukan COALESCE. COALESCE bukan fungsi deterministik, sehingga hasil ekspresi akan selalu dapat bernilai NULL. ISNULL berbeda. Ini deterministik. Oleh karena itu, ketika bagian kedua dari fungsi ISNULL adalah konstanta atau harfiah, nilai yang dihasilkan akan MENJADI NOT NULL.

Memastikan integritas perhitungan Anda juga penting untuk pengalihan partisi tabel. Bayangkan Anda memiliki tabel ini yang 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 himpunan 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 sempurna. Masalahnya datang ketika Anda mencoba melakukan pengalihan partisi. Definisi tabel tidak cocok. Untuk membuat definisi tabel cocok, ubah CTAS dengan menambahkan fungsi ISNULL untuk mempertahankan atribut nullability kolom.

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 tipe dan mempertahankan properti nullability pada CTAS adalah praktik terbaik rekayasa teknik. Ini membantu menjaga integritas dalam perhitungan Anda, dan juga memastikan bahwa pengalihan partisi dimungkinkan.

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

Untuk tips pengembangan selengkapnya, lihat gambaran umum pengembangan.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-05-03