Tabelle temporanee in SQL Data WarehouseTemporary tables in SQL Data Warehouse

Le tabelle temporanee sono molto utili durante l'elaborazione dati, soprattutto durante la trasformazione in cui i risultati intermedi sono temporanei.Temporary tables are very useful when processing data - especially during transformation where the intermediate results are transient. In SQL Data Warehouse le tabelle temporanee esistono a livello di sessione.In SQL Data Warehouse temporary tables exist at the session level. Sono visibili solo per la sessione in cui sono stati creati e vengono eliminati automaticamente quando si disconnette tale sessione.They are only visible to the session in which they were created and are automatically dropped when that session logs off. Le tabelle temporanee offrono un miglioramento delle prestazioni, perché i loro risultati vengono scritti in locale anziché nell'archiviazione remota.Temporary tables offer a performance benefit because their results are written to local rather than remote storage. Le tabelle temporanee sono leggermente diverse in SQL Data Warehouse di Azure rispetto al database SQL di Azure, poiché è possibile accedervi da un punto qualsiasi della sessione, sia dall'interno che dall'esterno di una stored procedure.Temporary tables are slightly different in Azure SQL Data Warehouse than Azure SQL Database as they can be accessed from anywhere inside the session, including both inside and outside of a stored procedure.

Questo articolo contiene le linee guida fondamentali per l'uso delle tabelle temporanee ed evidenzia i principi delle tabelle temporanee a livello di sessione.This article contains essential guidance for using temporary tables and highlights the principles of session level temporary tables. Usando le informazioni in questo articolo è possibile modularizzare il codice, aumentando le possibilità di riutilizzo e la facilità di manutenzione del codice.Using the information in this article can help you modularize your code, improving both reusability and ease of maintenance of your code.

Creazione di una tabella temporaneaCreate a temporary table

Le tabelle temporanee vengono create aggiungendo semplicemente un prefisso al nome di una tabella con #.Temporary tables are created by simply prefixing your table name with a #. ad esempio:For example:

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
)

Le tabelle temporanee possono essere create anche con CTAS adottando esattamente lo stesso approccio:Temporary tables can also be created with a CTAS using exactly the same approach:

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

Nota

CTAS è un comando molto efficace e offre l'ulteriore vantaggio di essere molto efficiente nell'uso dello spazio dei log delle transazioni.CTAS is a very powerful command and has the added advantage of being very efficient in its use of transaction log space.

Eliminazione delle tabelle temporaneeDropping temporary tables

Quando viene creata una nuova sessione, non deve esistere alcuna tabella temporanea.When a new session is created, no temporary tables should exist. Tuttavia, se si richiama la stessa stored procedure che crea una variabile temporanea con lo stesso nome, per garantire che le istruzioni CREATE TABLE abbiano esito positivo è possibile eseguire un controllo di pre-esistenza con DROP, come nel seguente esempio:However, if you are calling the same stored procedure, which creates a temporary with the same name, to ensure that your CREATE TABLE statements are successful a simple pre-existence check with a DROP can be used as in the below example:

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

Una procedura consigliata per la coerenza della codifica è usare questo modello per le tabelle e le tabelle temporanee.For coding consistency, it is a good practice to use this pattern for both tables and temporary tables. È inoltre consigliabile usare DROP TABLE per rimuovere le tabelle temporanee quando non sono più necessarie.It is also a good idea to use DROP TABLE to remove temporary tables when you have finished with them in your code. Nello sviluppo delle stored procedure è abbastanza comune visualizzare i comandi di eliminazione raggruppati in bundle alla fine di una procedura per garantire che questi oggetti vengano puliti.In stored procedure development it is quite 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

Modularizzazione del codiceModularizing code

Dal momento che le tabelle temporanee sono visibili in qualsiasi punto di una sessione utente, questo può essere sfruttato per modularizzare il codice dell'applicazione.Since temporary tables can be seen anywhere in a user session, this can be exploited to help you modularize your application code. Ad esempio, la stored procedure seguente riunisce le procedure consigliate riportate sopra per generare un DDL che aggiorna tutte le statistiche nel database in base al nome della statistica.For example, the stored procedure below brings together the recommended practices from above to generate DDL which will 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    t1
;
GO

In questa fase l'unica azione che si è verificata è la creazione di una stored procedure che genererà semplicemente una tabella temporanea, #stats_ddl, con le istruzioni DDL.At this stage the only action that has occurred is the creation of a stored procedure which will simply generated a temporary table, #stats_ddl, with DDL statements. Questa stored procedure eliminerà #stats_ddl se esiste già per garantire che non avrà esito negativo se eseguita più volte all'interno di una sessione.This stored procedure will drop #stats_ddl if it already exists to ensure it does not fail if run more than once within a session. Tuttavia, poiché non esiste DROP TABLE alla fine della stored procedure, al termine della stored procedure, la tabella creata verrà conservata in modo che possa essere letta all'esterno della stored procedure.However, since there is no DROP TABLE at the end of the stored procedure, when the stored procedure completes, it will leave the created table so that it can be read outside of the stored procedure. A differenza che negli altri server di database SQL, in SQL Data Warehouse è possibile usare la tabella temporanea all'esterno della procedura che l'ha creata.In SQL Data Warehouse, unlike other SQL Server databases, it is possible to use the temporary table outside of the procedure that created it. Le tabelle temporanee di SQL Data Warehouse possono essere usate ovunque all'interno della sessione.SQL Data Warehouse temporary tables can be used anywhere inside the session. In questo modo è possibile ottenere codice più modulare e gestibile come nel seguente esempio:This can lead to more modular and manageable code as in the below example:

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 della tabella temporaneaTemporary table limitations

SQL Data Warehouse impone un paio di limitazioni quando si implementano tabelle temporanee.SQL Data Warehouse does impose a couple of limitations when implementing temporary tables. Attualmente sono supportate solo le tabelle temporanee nell'ambito della sessione.Currently, only session scoped temporary tables are supported. Le tabelle temporanee globali non sono supportate.Global Temporary Tables are not supported. Inoltre, non è possibile creare visualizzazioni nelle tabelle temporanee.In addition, views cannot be created on temporary tables.

Passaggi successiviNext steps

Per altre informazioni, vedere gli articoli Panoramica delle tabelle, Tipi di dati per le tabelle, Distribuzione di una tabella, Indicizzazione di una tabella, Partizionamento di una tabella Gestione delle statistiche nelle tabelle.To learn more, see the articles on Table Overview, Table Data Types, Distributing a Table, Indexing a Table, Partitioning a Table and Maintaining Table Statistics. Per altre informazioni sulle procedure consigliate, vedere Procedure consigliate per SQL Data Warehouse.For more about best practices, see SQL Data Warehouse Best Practices.