Tabelle temporanee nel pool SQL dedicato in Azure Synapse Analytics

Questo articolo contiene indicazioni essenziali per l'uso di tabelle temporanee ed evidenzia i principi delle tabelle temporanee a livello di sessione.

L'uso delle informazioni contenute in questo articolo consente di modularizzare il codice, migliorando sia la riutilizzabilità che la facilità di manutenzione.

Che cosa sono le tabelle temporanee?

Le tabelle temporanee sono utili durante l'elaborazione dei dati, soprattutto durante la trasformazione in cui i risultati intermedi sono temporanei. Nel pool SQL dedicato le tabelle temporanee esistono a livello di sessione.

Le tabelle temporanee sono visibili solo alla sessione in cui sono state create e vengono eliminate automaticamente alla chiusura di tale sessione.

Le tabelle temporanee offrono un vantaggio per le prestazioni perché i risultati vengono scritti in locale anziché nell'archiviazione remota.

Tabelle temporanee nel pool SQL dedicato

Nella risorsa pool SQL dedicata le tabelle temporanee offrono un vantaggio in quanto i risultati vengono scritti in locale anziché in archiviazione remota.

Creare una tabella temporanea

Le tabelle temporanee vengono create anteponendo al nome della tabella un oggetto #. Per esempio:

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
)

È anche possibile creare tabelle temporanee con un CTAS usando esattamente lo stesso approccio:

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

Eliminare tabelle temporanee

Quando viene creata una nuova sessione, non dovrebbero esistere tabelle temporanee.

Se si chiama la stessa stored procedure, che crea una variabile temporanea con lo stesso nome, per assicurarsi che le istruzioni CREATE TABLE riescano è possibile eseguire un semplice controllo di preesistenza con DROP, come nell'esempio seguente:

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

Per la coerenza della codifica, è consigliabile usare questo modello sia per le tabelle che per le tabelle temporanee. È anche consigliabile usare DROP TABLE per rimuovere le tabelle temporanee al termine dell'uso nel codice.

Nello sviluppo di stored procedure, è comune vedere i comandi drop raggruppati alla fine di una procedura per garantire che questi oggetti vengano eliminati.

DROP TABLE #stats_ddl

Modularizzare il codice

Poiché le tabelle temporanee possono essere visualizzate ovunque in una sessione utente, questa funzionalità può essere usata per modularizzare il codice dell'applicazione.

Ad esempio, la stored procedure seguente genera un'istruzione DDL per aggiornare tutte le statistiche nel database in base al nome:

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 questa fase, l'unica azione che si è verificata è la creazione di una stored procedure che genera una tabella temporanea, #stats_ddl, con istruzioni DDL.

Questa stored procedure elimina un elemento #stats_ddl esistente per evitare che restituisca esito negativo se viene eseguita più volte in una sessione.

Tuttavia, poiché non c'è DROP TABLE alla fine della stored procedure, quando la stored procedure termina, lascia la tabella creata in modo che possa essere letta al di fuori della stored procedure.

Nel pool SQL dedicato, a differenza di altri database di SQL Server, è possibile usare la tabella temporanea all'esterno della procedura che l'ha creata. Le tabelle temporanee del pool SQL dedicato possono essere usate ovunque all'interno della sessione. Questa funzionalità può portare a codice più modulare e gestibile, come nell'esempio seguente:

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;

Limitazioni delle tabelle temporanee

Il pool SQL dedicato impone un paio di limitazioni durante l'implementazione di tabelle temporanee. Attualmente sono supportate solo le tabelle temporanee con ambito sessione. Le tabelle temporanee globali non sono supportate.

Inoltre, le viste non possono essere create nelle tabelle temporanee. È possibile creare tabelle temporanee solo con distribuzione hash o round robin. La distribuzione di tabelle temporanee replicate non è supportata.

Passaggi successivi

Per altre informazioni sullo sviluppo di tabelle, vedere l'articolo Progettazione di tabelle con pool SQL dedicato .