Tijdelijke tabellen in Synapse SQL

2025-04-07

Dit artikel bevat essentiële richtlijnen voor het gebruik van tijdelijke tabellen en markeert de principes van tijdelijke tabellen op sessieniveau in Synapse SQL.

Zowel de toegewezen SQL-pool als serverloze SQL-poolbronnen kunnen tijdelijke tabellen gebruiken. Serverloze SQL-pool heeft beperkingen die aan het einde van dit artikel worden besproken.

Tijdelijke tabellen

Tijdelijke tabellen zijn handig bij het verwerken van gegevens, met name tijdens de transformatie waarbij de tussenliggende resultaten tijdelijk zijn. Met Synapse SQL bestaan tijdelijke tabellen op sessieniveau. Ze zijn alleen zichtbaar voor de sessie waarin ze zijn gemaakt. Als zodanig worden ze automatisch verwijderd wanneer die sessie afloopt.

Tijdelijke tabellen in toegewezen SQL-pool

In de toegewezen SQL-poolresource bieden tijdelijke tabellen een prestatievoordeel omdat hun resultaten worden weggeschreven naar lokale in plaats van externe opslag.

Een tijdelijke tabel maken

Tijdelijke tabellen worden gemaakt door de tabelnaam vooraf te laten gaan door een #. Voorbeeld:

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
)

Tijdelijke tabellen kunnen ook worden gemaakt met dezelfde CTAS methode:

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]
)
;

Opmerking

CTAS is een krachtige opdracht en heeft het extra voordeel dat het efficiënt is in het gebruik van transactielogboekruimte.

Tijdelijke tabellen verwijderen

Wanneer er een nieuwe sessie wordt gemaakt, mogen er geen tijdelijke tabellen bestaan. Als u echter dezelfde opgeslagen procedure aanroept die een tijdelijke tabel met dezelfde naam maakt, om ervoor te zorgen dat uw CREATE TABLE instructies slagen, gebruikt u een eenvoudige controle vooraf met DROP.

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

Voor het coderen van consistentie is het een goed idee om dit patroon te gebruiken voor zowel tabellen als tijdelijke tabellen. Het is ook een goed idee om DROP TABLE te gebruiken om tijdelijke tabellen te verwijderen wanneer u er klaar mee bent.

Bij het ontwikkelen van opgeslagen procedures is het gebruikelijk dat de verwijderingsopdrachten aan het einde van een procedure gebundeld worden, zodat deze objecten worden verwijderd.

DROP TABLE #stats_ddl

Code modulariseren

Tijdelijke tabellen kunnen overal in een gebruikerssessie worden gebruikt. Deze mogelijkheid kan vervolgens worden misbruikt om u te helpen uw toepassingscode te modulariseren. Als voorbeeld genereert de volgende opgeslagen procedure DDL om alle statistieken in de database per statistieknaam bij te werken.

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

In deze fase is de enige actie die is opgetreden het maken van een opgeslagen procedure waarmee de tijdelijke tabel #stats_ddl wordt gegenereerd. De opgeslagen procedure verwijdert #stats_ddl als die al bestaat. Deze daling zorgt ervoor dat deze niet mislukt als deze meer dan één keer binnen een sessie wordt uitgevoerd.

Omdat er geen DROP TABLE aan het einde van de opgeslagen procedure is, blijft de gemaakte tabel behouden en kan deze buiten de opgeslagen procedure worden gelezen wanneer de opgeslagen procedure is voltooid.

In tegenstelling tot andere SQL Server-databases kunt u met Synapse SQL de tijdelijke tabel gebruiken buiten de procedure waarmee deze is gemaakt. De tijdelijke tabellen die zijn gemaakt via een toegewezen SQL-pool, kunnen overal in de sessie worden gebruikt. Als gevolg hiervan hebt u meer modulaire en beheerbare code, zoals wordt weergegeven in het onderstaande voorbeeld:

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;

Tijdelijke tabelbeperkingen

Toegewezen SQL-pool heeft enkele implementatiebeperkingen voor tijdelijke tabellen:

Alleen tijdelijke tabellen met sessiebereik worden ondersteund. Globale tijdelijke tabellen worden niet ondersteund.
Weergaven kunnen niet worden gemaakt op tijdelijke tabellen.
Tijdelijke tabellen kunnen alleen worden gemaakt met een hash- of round-robinverdeling. Gerepliceerde tijdelijke tabeldistributie wordt niet ondersteund.

Tijdelijke tabellen in een serverloze SQL-pool

Tijdelijke tabellen in een serverloze SQL-pool worden ondersteund, maar het gebruik ervan is beperkt. Ze kunnen niet worden gebruikt in query's die zijn gericht op bestanden.

U kunt bijvoorbeeld geen tijdelijke tabel koppelen met gegevens uit bestanden in opslag. Het aantal tijdelijke tabellen is beperkt tot 100 en de totale grootte is beperkt tot 100 MB.

Volgende stappen

Zie het artikel Over het ontwerpen van tabellen met behulp van het artikel Synapse SQL-resources voor meer informatie over het ontwikkelen van tabellen.

Delen via

Tijdelijke tabellen in Synapse SQL