Tabel sementara dalam kumpulan SQL khusus di Azure Synapse Analytics

Artikel
06/01/2023

Artikel ini berisi panduan penting penggunaan tabel sementara dan menyoroti prinsip-prinsip tabel sementara tingkat sesi.

Menggunakan informasi dalam artikel ini bisa membantu Anda memodulasi kode Anda, meningkatkan penggunaan kembali dan kemudahan pemeliharaan.

Apa yang dimaksud tabel sementara?

Tabel sementara berguna saat memproses data, terutama selama transformasi ketika hasil antara bersifat sementara. Di kumpulan SQL khusus, tabel sementara berada di tingkat sesi.

Tabel sementara hanya terlihat dalam sesi tempat tabel tersebut dibuat dan secara otomatis dihilangkan saat sesi tersebut ditutup.

Tabel sementara menawarkan manfaat kinerja karena hasilnya ditulis pada penyimpanan lokal alih-alih jarak jauh.

Tabel sementara di kumpulan SQL khusus

Dalam sumber daya kumpulan SQL khusus, tabel sementara menawarkan manfaat kinerja karena hasilnya ditulis pada penyimpanan lokal alih-alih 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]
)
;

Catatan

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

Menghilangkan tabel sementara

Ketika sesi baru dibuat, tidak akan ada tabel sementara.

Jika Anda memanggil prosedur tersimpan yang sama, yang membuat sementara dengan nama yang sama, untuk memastikan bahwa CREATE TABLE pernyataan Anda berhasil, pemeriksaan pra-keberadaan sederhana dengan DROP dapat digunakan seperti dalam contoh berikut:

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

Untuk konsistensi pengkodean, disarankan untuk menggunakan pola ini untuk tabel dan tabel sementara. Ada baiknya juga untuk menggunakan DROP TABLE untuk menghapus tabel sementara setelah selesai menggunakannya dalam kode Anda.

Dalam pengembangan prosedur tersimpan, melihat perintah drop yang dibundel bersama di akhir prosedur biasa dilakukan untuk memastikan objek-objek ini dibersihkan.

DROP TABLE #stats_ddl

Kode modularisasi

Karena tabel sementara dapat dilihat di mana saja dalam sesi pengguna, kemampuan ini bisa dimanfaatkan untuk membantu Anda memodulasi kode aplikasi Anda.

Contohnya, 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, dengan pernyataan DDL.

Prosedur tersimpan ini menghilangkan #stats_ddl yang sudah ada untuk memastikannya tidak gagal jika berjalan lebih dari sekali dalam satu sesi.

Namun, karena tidak ada DROP TABLE di akhir prosedur tersimpan, saat prosedur tersimpan selesai, ia meninggalkan tabel yang dibuat agar dapat dibaca di luar prosedur tersimpan.

Dalam kumpulan SQL khusus, tidak seperti database SQL Server lainnya, penggunaan tabel sementara di luar prosedur bisa dilakukan. Tabel sementara kumpulan SQL khusus dapat digunakan di mana saja di dalam sesi. Fitur ini dapat mengarah ke kode yang lebih modular dan dapat dikelola seperti pada contoh berikut:

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 memberlakukan beberapa keterbatasan saat menerapkan tabel sementara. Saat ini, hanya sesi yang mencakup tabel sementara yang didukung. Tabel Sementara Global tidak didukung.

Selain itu, tampilan tidak bisa dibuat pada tabel sementara. Tabel sementara hanya bisa dibuat dengan distribusi hash atau round robin. Distribusi tabel sementara yang direplikasi tidak didukung.

Langkah berikutnya

Untuk mempelajari lebih lanjut tentang mengembangkan tabel, lihat artikel Merancang tabel menggunakan kumpulan SQL khusus.

Bagikan melalui