Tabele tymczasowe w usłudze Synapse SQL
Ten artykuł zawiera podstawowe wskazówki dotyczące używania tabel tymczasowych i wyróżnia zasady tabel tymczasowych na poziomie sesji w usłudze Synapse SQL.
Zarówno dedykowana pula SQL, jak i zasoby bezserwerowej puli SQL mogą korzystać z tabel tymczasowych. Bezserwerowa pula SQL ma ograniczenia omówione na końcu tego artykułu.
Tabele tymczasowe
Tabele tymczasowe są przydatne podczas przetwarzania danych, zwłaszcza podczas przekształcania, gdy wyniki pośrednie są przejściowe. W przypadku usługi Synapse SQL tabele tymczasowe istnieją na poziomie sesji. Są one widoczne tylko dla sesji, w której zostały utworzone. W związku z tym są one automatycznie porzucane po zakończeniu tej sesji.
Tabele tymczasowe w dedykowanej puli SQL
W dedykowanym zasobie puli SQL tabele tymczasowe oferują korzyść wydajności, ponieważ ich wyniki są zapisywane w magazynie lokalnym, a nie w magazynie zdalnym.
Tworzenie tabeli tymczasowej
Tabele tymczasowe są tworzone przez prefiks nazwy tabeli z .# Przykład:
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
)
Tabele tymczasowe można również utworzyć CTAS przy użyciu dokładnie tego samego podejścia:
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]
)
;
Uwaga
CTAS jest zaawansowanym poleceniem i ma dodatkową zaletę efektywnego korzystania z przestrzeni dziennika transakcji.
Usuwanie tabel tymczasowych
Po utworzeniu nowej sesji nie powinny istnieć tabele tymczasowe. Jeśli jednak wywołujesz tę samą procedurę składowaną, która tworzy tymczasową nazwę o tej samej nazwie, aby upewnić się, że CREATE TABLE instrukcje zakończyły się pomyślnie, użyj prostego sprawdzania wstępnego istnienia z poleceniem DROP:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
Aby zapewnić spójność kodowania, dobrym rozwiązaniem jest użycie tego wzorca zarówno dla tabel, jak i tabel tymczasowych. Dobrym pomysłem DROP TABLE jest również usunięcie tabel tymczasowych po zakończeniu pracy z nimi.
W przypadku opracowywania procedur składowanych często są wyświetlane polecenia upuszczania połączone na końcu procedury w celu zapewnienia, że te obiekty są czyszczone.
DROP TABLE #stats_ddl
Modularyzacja kodu
Tabele tymczasowe mogą być używane w dowolnym miejscu w sesji użytkownika. Ta funkcja może zostać wykorzystana w celu ułatwienia modularyzacji kodu aplikacji. Aby to zademonstrować, poniższa procedura składowana generuje kod DDL w celu zaktualizowania wszystkich statystyk w bazie danych według nazwy statystyki:
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
Na tym etapie jedyną akcją, która wystąpiła, jest utworzenie procedury składowanej, która generuje #stats_ddl tabeli tymczasowej. Procedura składowana spada #stats_ddl, jeśli już istnieje. Ten spadek gwarantuje, że nie powiedzie się, jeśli zostanie uruchomiony więcej niż raz w ramach sesji.
Ponieważ nie DROP TABLE ma elementu na końcu procedury składowanej, po zakończeniu procedury składowanej utworzona tabela pozostaje i można ją odczytać poza procedurą składowaną.
W przeciwieństwie do innych baz danych SQL Server usługa Synapse SQL umożliwia użycie tabeli tymczasowej poza procedurą, która ją utworzyła. Tabele tymczasowe utworzone za pośrednictwem dedykowanej puli SQL mogą być używane w dowolnym miejscu w sesji. W związku z tym będziesz mieć bardziej modułowy i możliwy do zarządzania kod, jak pokazano w poniższym przykładzie:
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;
Ograniczenia tabeli tymczasowej
Dedykowana pula SQL ma kilka ograniczeń implementacji dla tabel tymczasowych:
- Obsługiwane są tylko tabele tymczasowe o zakresie sesji. Globalne tabele tymczasowe nie są obsługiwane.
- Nie można tworzyć widoków w tabelach tymczasowych.
- Tabele tymczasowe można tworzyć tylko przy użyciu dystrybucji skrótu lub działania okrężnego. Replikowana tymczasowa dystrybucja tabel nie jest obsługiwana.
Tabele tymczasowe w bezserwerowej puli SQL
Tabele tymczasowe w bezserwerowej puli SQL są obsługiwane, ale ich użycie jest ograniczone. Nie można ich używać w zapytaniach skierowanych do plików.
Na przykład nie można dołączyć tabeli tymczasowej z danymi z plików w magazynie. Liczba tabel tymczasowych jest ograniczona do 100, a ich całkowity rozmiar jest ograniczony do 100 MB.
Następne kroki
Aby dowiedzieć się więcej na temat tworzenia tabel, zobacz artykuł Projektowanie tabel przy użyciu zasobów usługi Synapse SQL .