Anteckning
Åtkomst till den här sidan kräver auktorisering. Du kan prova att logga in eller ändra kataloger.
Åtkomst till den här sidan kräver auktorisering. Du kan prova att ändra kataloger.
Den här artikeln innehåller viktig vägledning för att använda tillfälliga tabeller och belyser principerna för temporära tabeller på sessionsnivå i Synapse SQL.
Både den dedikerade SQL-poolen och serverlösa SQL-poolresurser kan använda tillfälliga tabeller. Serverlös SQL-pool har begränsningar som beskrivs i slutet av den här artikeln.
Temporära tabeller
Temporära tabeller är användbara vid bearbetning av data, särskilt under transformering där mellanliggande resultat är tillfälliga. Med Synapse SQL finns tillfälliga tabeller på sessionsnivå. De är bara synliga för sessionen där de skapades. Därför tas de bort automatiskt när sessionen avslutas.
Temporära tabeller i en dedikerad SQL-pool
I den dedikerade SQL-poolresursen erbjuder tillfälliga tabeller en prestandaförmån eftersom deras resultat skrivs till lokal i stället för fjärrlagring.
Skapa en tillfällig tabell
Temporära tabeller skapas genom att prefixera tabellnamnet med en #
. Till 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
)
Tillfälliga tabeller kan också skapas med en CTAS
genom att använda exakt 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]
)
;
Anmärkning
CTAS
är ett kraftfullt kommando och har den extra fördelen att vara effektiv i sin användning av transaktionsloggutrymme.
Ta bort temporära tabeller
När en ny session skapas ska det inte finnas några tillfälliga tabeller. However, if you're calling the same stored procedure that creates a temporary with the same name, to ensure that your CREATE TABLE
statements are successful, use a simple pre-existence check with DROP
:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
För kodningskonsekvens ä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.
In stored procedure development, it's common to see the drop commands bundled together at the end of a procedure to ensure these objects are cleaned up.
DROP TABLE #stats_ddl
Modularisera kod
Tillfälliga tabeller kan användas var som helst i en användarsession. Den här funktionen kan sedan utnyttjas för att hjälpa dig att modularisera programkoden. To demonstrate, the following stored procedure generates DDL to update all statistics in the database by statistic name:
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ärd som har inträffat att skapa en lagrad procedur som genererar den tillfälliga tabellen #stats_ddl. Den lagrade proceduren tar bort #stats_ddl om den redan finns. This drop ensures it doesn't fail if run more than once within a session.
Eftersom det inte finns någon DROP TABLE
i slutet av den lagrade proceduren finns den skapade tabellen kvar och kan läsas utanför den lagrade proceduren när den lagrade proceduren är klar.
Till skillnad från andra SQL Server-databaser kan du med Synapse SQL använda den temporära tabellen utanför proceduren som skapade den. De temporära tabeller som skapas via en dedikerad SQL-pool kan användas var som helst i sessionen. Därför har du mer modulär och hanterbar kod, vilket visas i exemplet nedan:
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;
Temporära tabellbegränsningar
Dedikerad SQL-pool har några implementeringsbegränsningar för tillfälliga tabeller:
- Endast temporära tabeller med sessionsomfattning stöds. Globala temporära tabeller stöds inte.
- Det går inte att skapa vyer i temporära tabeller.
- Tillfälliga tabeller kan bara skapas med hash- eller round-robin-distribution. Replikerad tillfällig tabelldistribution stöds inte.
Temporära tabeller i en serverlös SQL-pool
Temporära tabeller i en serverlös SQL-pool stöds, men användningen är begränsad. De kan inte användas i frågor riktade mot filer.
Du kan till exempel inte ansluta till en tillfällig tabell med data från filer i lagringen. Antalet temporära tabeller är begränsat till 100 och deras totala storlek är begränsad till 100 MB.
Nästa steg
Mer information om hur du utvecklar tabeller finns i artikeln Designa tabeller med Hjälp av Synapse SQL-resurser .