Tabel sementara di Synapse SQL

Artikel ini berisi panduan penting untuk menggunakan tabel sementara dan menyoroti prinsip-prinsip tabel sementara tingkat sesi dalam Synapse SQL.

Kumpulan SQL khusus dan sumber daya kumpulan SQL tanpa server dapat menggunakan tabel sementara. Kumpulan SQL tanpa server memiliki batasan yang dibahas di akhir artikel ini.

Tabel sementara

Tabel sementara berguna saat memproses data, terutama selama transformasi di mana hasil perantara bersifat sementara. Dengan Synapse SQL, tabel sementara ada di tingkat sesi. Mereka hanya terlihat oleh sesi di mana sesi tersebut dibuat. Dengan demikian, mereka secara otomatis dihapus ketika sesi berakhir.

Tabel sementara di kumpulan SQL khusus

Dalam sumber daya kumpulan SQL khusus, tabel sementara menawarkan manfaat performa karena hasilnya ditulis ke penyimpanan lokal daripada penyimpanan jarak jauh.

Membuat tabel sementara

Tabel sementara dibuat dengan awalan nama tabel Anda dengan #. Contohnya:

CREATE TABLE #stats_ddl
(
    [schema_name]        NVARCHAR(128) NOT NULL
,    [table_name]            NVARCHAR(128) NOT NULL
,    [stats_name]            NVARCHAR(128) NOT NULL
,    [stats_is_filtered]     BIT           NOT NULL
,    [seq_nmbr]              BIGINT        NOT NULL
,    [two_part_name]         NVARCHAR(260) NOT NULL
,    [three_part_name]       NVARCHAR(400) NOT NULL
)
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
,    HEAP
)

Tabel sementara juga dapat dibuat dengan CTAS menggunakan pendekatan yang sama persis:

CREATE TABLE #stats_ddl
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
,    HEAP
)
AS
(
SELECT
        sm.[name]                                                                AS [schema_name]
,        tb.[name]                                                                AS [table_name]
,        st.[name]                                                                AS [stats_name]
,        st.[has_filter]                                                            AS [stats_is_filtered]
,       ROW_NUMBER()
        OVER(ORDER BY (SELECT NULL))                                            AS [seq_nmbr]
,                                 QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [two_part_name]
,        QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [three_part_name]
FROM    sys.objects            AS ob
JOIN    sys.stats            AS st    ON    ob.[object_id]        = st.[object_id]
JOIN    sys.stats_columns    AS sc    ON    st.[stats_id]        = sc.[stats_id]
                                    AND st.[object_id]        = sc.[object_id]
JOIN    sys.columns            AS co    ON    sc.[column_id]        = co.[column_id]
                                    AND    sc.[object_id]        = co.[object_id]
JOIN    sys.tables            AS tb    ON    co.[object_id]        = tb.[object_id]
JOIN    sys.schemas            AS sm    ON    tb.[schema_id]        = sm.[schema_id]
WHERE    1=1
AND        st.[user_created]   = 1
GROUP BY
        sm.[name]
,        tb.[name]
,        st.[name]
,        st.[filter_definition]
,        st.[has_filter]
)
;

Nota

CTAS adalah perintah yang kuat dan memiliki keuntungan tambahan untuk menjadi efisien dalam penggunaan ruang log transaksi.

Menghilangkan tabel sementara

Ketika sesi baru dibuat, tidak boleh ada tabel sementara. Namun, jika Anda memanggil prosedur tersimpan yang sama dan membuat tabel sementara dengan nama yang sama, untuk memastikan bahwa pernyataan CREATE TABLE Anda berhasil, gunakan pemeriksaan pra-keberadaan sederhana dengan DROP:

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

Untuk konsistensi pengkodian, ini adalah praktik yang baik untuk menggunakan pola ini untuk tabel dan tabel sementara. Ada baiknya juga menggunakan DROP TABLE untuk menghapus tabel sementara saat Anda selesai dengan tabel tersebut.

Dalam pengembangan prosedur simpanan, umumnya perintah drop digabungkan di akhir prosedur untuk memastikan pembersihan objek.

DROP TABLE #stats_ddl

Memodularisasi kode

Tabel sementara dapat digunakan di mana saja dalam sesi pengguna. Kemampuan ini kemudian dapat dieksploitasi untuk membantu Anda memodulasi kode aplikasi Anda. Sebagai contoh, prosedur tersimpan berikut menghasilkan DDL untuk memperbarui semua statistik dalam database berdasarkan nama statistik:

CREATE PROCEDURE    [dbo].[prc_sqldw_update_stats]
(   @update_type    tinyint -- 1 default 2 fullscan 3 sample 4 resample
    ,@sample_pct     tinyint
)
AS

IF @update_type NOT IN (1,2,3,4)
BEGIN;
    THROW 151000,'Invalid value for @update_type parameter. Valid range 1 (default), 2 (fullscan), 3 (sample) or 4 (resample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

CREATE TABLE #stats_ddl
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
)
AS
(
SELECT
        sm.[name]                                                                AS [schema_name]
,        tb.[name]                                                                AS [table_name]
,        st.[name]                                                                AS [stats_name]
,        st.[has_filter]                                                            AS [stats_is_filtered]
,       ROW_NUMBER()
        OVER(ORDER BY (SELECT NULL))                                            AS [seq_nmbr]
,                                 QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [two_part_name]
,        QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [three_part_name]
FROM    sys.objects            AS ob
JOIN    sys.stats            AS st    ON    ob.[object_id]        = st.[object_id]
JOIN    sys.stats_columns    AS sc    ON    st.[stats_id]        = sc.[stats_id]
                                    AND st.[object_id]        = sc.[object_id]
JOIN    sys.columns            AS co    ON    sc.[column_id]        = co.[column_id]
                                    AND    sc.[object_id]        = co.[object_id]
JOIN    sys.tables            AS tb    ON    co.[object_id]        = tb.[object_id]
JOIN    sys.schemas            AS sm    ON    tb.[schema_id]        = sm.[schema_id]
WHERE    1=1
AND        st.[user_created]   = 1
GROUP BY
        sm.[name]
,        tb.[name]
,        st.[name]
,        st.[filter_definition]
,        st.[has_filter]
)
SELECT
    CASE @update_type
    WHEN 1
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+');'
    WHEN 2
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH FULLSCAN;'
    WHEN 3
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH SAMPLE '+CAST(@sample_pct AS VARCHAR(20))+' PERCENT;'
    WHEN 4
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH RESAMPLE;'
    END AS [update_stats_ddl]
,   [seq_nmbr]
FROM    #stats_ddl
;
GO

Pada tahap ini, satu-satunya tindakan yang telah terjadi adalah pembuatan prosedur tersimpan yang menghasilkan tabel sementara #stats_ddl. Prosedur simpan menghapus #stats_ddl jika sudah ada. Pengaturan ini memastikan tidak terjadi kegagalan jika dijalankan lebih dari sekali dalam satu sesi.

Karena tidak ada DROP TABLE di akhir prosedur tersimpan, ketika prosedur tersimpan selesai, tabel yang dibuat tetap ada dan dapat dibaca di luar prosedur tersimpan.

Berbeda dengan database SQL Server lainnya, Synapse SQL memungkinkan Anda menggunakan tabel sementara di luar prosedur yang membuatnya. Tabel sementara yang dibuat melalui kumpulan SQL khusus dapat digunakan di mana saja di dalam sesi. Akibatnya, Anda akan memiliki lebih banyak kode modular dan dapat dikelola, seperti yang ditunjukkan dalam sampel di bawah ini:

EXEC [dbo].[prc_sqldw_update_stats] @update_type = 1, @sample_pct = NULL;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''

WHILE @i <= @t
BEGIN
    SET @s=(SELECT update_stats_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

Batasan tabel sementara

Kumpulan SQL khusus memang memiliki beberapa batasan implementasi untuk tabel sementara:

Hanya tabel sementara yang dilingkup sesi yang didukung. Tabel Global Sementara tidak didukung.
Tampilan tidak dapat dibuat pada tabel sementara.
Tabel sementara hanya dapat dibuat dengan distribusi "hash" atau "round robin". Distribusi tabel sementara yang direplikasi tidak didukung.

Tabel sementara di kumpulan SQL tanpa server

Tabel sementara di kumpulan SQL tanpa server didukung tetapi penggunaannya terbatas. Tabel tersebut tidak dapat digunakan dalam kueri yang menargetkan file.

Misalnya, Anda tidak dapat menggabungkan tabel sementara dengan data dari file dalam penyimpanan. Jumlah tabel sementara dibatasi hingga 100, dan ukuran totalnya dibatasi hingga 100 MB.

Langkah berikutnya

Untuk mempelajari selengkapnya tentang mengembangkan tabel, lihat artikel Merancang tabel menggunakan sumber daya Synapse SQL.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-04-01