Temporära tabeller i en dedikerad SQL-pool i Azure Synapse Analytics
Den här artikeln innehåller viktig vägledning för att använda temporära tabeller och belyser principerna för temporära tabeller på sessionsnivå.
Med hjälp av informationen i den här artikeln kan du modularisera din kod, vilket förbättrar både återanvändning och enkelt underhåll.
Vad är temporära tabeller?
Temporära tabeller är användbara när du bearbetar data, särskilt under transformering där mellanliggande resultat är tillfälliga. I en dedikerad SQL-pool finns temporära tabeller på sessionsnivå.
Temporära tabeller visas bara för den session där de skapades och tas bort automatiskt när sessionen stängs.
Temporära tabeller ger en prestandafördel eftersom deras resultat skrivs till lokal i stället för till fjärrlagring.
Temporära tabeller i en dedikerad SQL-pool
I den dedikerade SQL-poolresursen erbjuder temporära tabeller en prestandafördel eftersom deras resultat skrivs till lokal i stället för till fjärrlagring.
Skapa en tillfällig tabell
Temporära tabeller skapas genom prefixet för tabellnamnet med en #. Exempel:
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
)
Temporära tabeller kan också skapas med exakt CTAS samma metod:
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]
)
;
Anteckning
CTAS är ett kraftfullt kommando och har den extra fördelen att det är effektivt att använda transaktionsloggutrymmet.
Ta bort temporära tabeller
När en ny session skapas bör det inte finnas några temporära tabeller.
Om du anropar samma lagrade procedur, vilket skapar en tillfällig med samma namn, för att säkerställa att dina CREATE TABLE -instruktioner lyckas, kan en enkel kontroll med en DROP förefintlighet användas som i följande exempel:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
För kodkonsekvens är det en bra idé att använda det här mönstret för både tabeller och temporära tabeller. Det är också en bra idé att använda DROP TABLE för att ta bort temporära tabeller när du är klar med dem i koden.
I utvecklingen av lagrade procedurer är det vanligt att se de nedrullningsbara kommandon som paketeras tillsammans i slutet av en procedur för att säkerställa att dessa objekt rensas.
DROP TABLE #stats_ddl
Modularisera kod
Eftersom temporära tabeller kan ses var som helst i en användarsession kan den här funktionen användas för att hjälpa dig att modularisera programkoden.
Följande lagrade procedur genererar till exempel DDL för att uppdatera all statistik i databasen efter statistiknamn:
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
I det här skedet är den enda åtgärden som har inträffat att skapa en lagrad procedur som genererar en tillfällig tabell, #stats_ddl, med DDL-instruktioner.
Den här lagrade proceduren tar bort en befintlig #stats_ddl för att säkerställa att den inte misslyckas om den körs mer än en gång inom en session.
Men eftersom det inte finns någon DROP TABLE i slutet av den lagrade proceduren, när den lagrade proceduren slutförs, lämnar den skapade tabellen så att den kan läsas utanför den lagrade proceduren.
I en dedikerad SQL-pool, till skillnad från andra SQL Server databaser, går det att använda den tillfälliga tabellen utanför proceduren som skapade den. Temporära tabeller för dedikerad SQL-pool kan användas var som helst i sessionen. Den här funktionen kan leda till mer modulär och hanterbar kod som i följande exempel:
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;
Begränsningar för temporära tabeller
En dedikerad SQL-pool medför ett par begränsningar när du implementerar temporära tabeller. För närvarande stöds endast temporära tabeller med sessionsomfång. Globala temporära tabeller stöds inte.
Det går inte heller att skapa vyer i temporära tabeller. Temporära tabeller kan bara skapas med hash- eller resursallokeringsdistribution. Replikerad tillfällig tabelldistribution stöds inte.
Nästa steg
Mer information om hur du utvecklar tabeller finns i artikeln Designa tabeller med dedikerad SQL-pool .