Tabel sementara di Synapse SQL
Artikel ini berisi panduan penting penggunaan tabel sementara dan menyoroti prinsip-prinsip tabel sementara tingkat sesi dalam Synapse SQL.
Baik 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 ketika hasil antara bersifat sementara. Dengan Synapse SQL, tabel sementara ada di tingkat sesi. Tabel hanya terlihat oleh sesi di mana tabel tersebut dibuat. Dengan demikian, tabel secara otomatis dihapus saat sesi tersebut berakhir.
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. Namun, jika Anda memanggil prosedur tersimpan yang sama, yang membuat sementara dengan nama yang sama, untuk memastikan bahwa CREATE TABLE pernyataan Anda berhasil, gunakan pemeriksaan prakeberadaan sederhana dengan DROP:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
Untuk konsistensi pengodean, sebaiknya gunakan pola ini untuk tabel dan tabel sementara. Sebaiknya gunakan juga DROP TABLE untuk menghapus tabel sementara setelah selesai menggunakannya.
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
Tabel sementara dapat digunakan di mana saja dalam sesi pengguna. Kemampuan ini kemudian dapat dimanfaatkan untuk membantu Anda memodulasi kode aplikasi. Untuk menunjukkan, 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 yang tersimpan menurunkan #stats_ddl jika sudah ada. Penurunan ini memastikan tidak gagal jika berjalan lebih dari sekali dalam sesi.
Karena tidak ada DROP TABLE di akhir prosedur tersimpan, saat 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 pun 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 penerapan untuk tabel sementara:
- Hanya sesi yang mencakup tabel sementara yang didukung. Tabel Sementara Global tidak didukung.
- Tampilan tidak dapat dibuat pada tabel sementara.
- Tabel sementara hanya bisa 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 bergabung dengan tabel sementara dengan data dari file di 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 kumpulan Synapse SQL.
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk