Delen via


Tijdelijke tabellen in Synapse SQL

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

Zowel de toegewezen SQL-pool als de serverloze SQL-poolresources kunnen gebruikmaken van tijdelijke tabellen. 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 transformaties 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 wordt beëindigd.

Tijdelijke tabellen in toegewezen SQL-pool

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

Een tijdelijke tabel maken

Tijdelijke tabellen worden gemaakt door het voorvoegsel van uw tabelnaam te voorzien van een #. Bijvoorbeeld:

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 een CTAS met exact dezelfde aanpak:

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

Notitie

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

Tijdelijke tabellen verwijderen

Wanneer een nieuwe sessie wordt gemaakt, mogen er geen tijdelijke tabellen bestaan. Als u echter dezelfde opgeslagen procedure aanroept waarmee een tijdelijke procedure met dezelfde naam wordt gemaakt, gebruikt CREATE TABLE u een eenvoudige controle voor het bestaan van uw instructies met DROP:

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

Voor codeconsistentie 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 tijdelijke tabellen te verwijderen wanneer u er klaar mee bent.

Bij het ontwikkelen van opgeslagen procedures is het gebruikelijk dat de opdrachten voor neerzetten aan het einde van een procedure worden gebundeld om ervoor te zorgen dat deze objecten worden opgeschoond.

DROP TABLE #stats_ddl

Code modulariseren

Tijdelijke tabellen kunnen overal in een gebruikerssessie worden gebruikt. Deze mogelijkheid kan vervolgens worden gebruikt om u te helpen uw toepassingscode modulariseren. Ter illustratie genereert de volgende opgeslagen procedure DDL om alle statistieken in de database bij te werken op statistische naam:

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 #stats_ddl tijdelijke tabel wordt gegenereerd. De opgeslagen procedure wordt #stats_ddl als deze al bestaat. Deze daling zorgt ervoor dat deze niet mislukt als het 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 na voltooiing van de opgeslagen procedure behouden en kan deze buiten de opgeslagen procedure worden gelezen.

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 in het onderstaande voorbeeld wordt gedemonstreerd:

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.
  • Er kunnen geen weergaven worden gemaakt voor tijdelijke tabellen.
  • Tijdelijke tabellen kunnen alleen worden gemaakt met hash- of round robin-distributie. Gerepliceerde tijdelijke tabeldistributie wordt niet ondersteund.

Tijdelijke tabellen in 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 Tabellen ontwerpen met behulp van synapse SQL-resources voor meer informatie over het ontwikkelen van tabellen.