Tables temporaires dans SQL Data WarehouseTemporary tables in SQL Data Warehouse

Cet article contient des conseils de base pour l’utilisation des tables temporaires et met en évidence les principes des tables temporaires au niveau de la session.This article contains essential guidance for using temporary tables and highlights the principles of session level temporary tables. L’utilisation des informations de cet article peut vous aider à modulariser votre code, et à améliorer sa réutilisabilité et sa facilité de maintenance.Using the information in this article can help you modularize your code, improving both reusability and ease of maintenance of your code.

Qu’est-ce que les tables temporaires ?What are temporary tables?

Les tables temporaires sont utiles lors du traitement des données, notamment lors d’une transformation, lorsque les résultats intermédiaires sont temporaires.Temporary tables are useful when processing data - especially during transformation where the intermediate results are transient. Les tables temporaires se trouvent au niveau de la session dans SQL Data Warehouse.In SQL Data Warehouse, temporary tables exist at the session level. Elles sont uniquement visibles dans la session dans laquelle elles ont été créées et sont automatiquement supprimées lorsque cette session se déconnecte.They are only visible to the session in which they were created and are automatically dropped when that session logs off. Les tables temporaires offrent un gain de performances, car leurs résultats sont écrits en local et non dans un stockage distant.Temporary tables offer a performance benefit because their results are written to local rather than remote storage.

Créer une table temporaireCreate a temporary table

Les tables temporaires sont créées en faisant simplement précéder le nom de votre table de #.Temporary tables are created by prefixing your table name with a #. Par exemple :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
)

Vous pouvez également utiliser CTAS pour créer des tables temporaires à l’aide de la même approche :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]
)
;

Notes

CTAS est une commande puissante et présente l’avantage d’être efficace dans son utilisation de l’espace de journal des transactions.CTAS is a powerful command and has the added advantage of being efficient in its use of transaction log space.

Suppression de tables temporairesDropping temporary tables

Lorsqu’une nouvelle session est créée, aucune table temporaire ne doit exister.When a new session is created, no temporary tables should exist. Toutefois, si vous appelez la même procédure stockée, qui crée une table temporaire avec le même nom, pour vous assurer de la réussite de vos instructions CREATE TABLE, une simple vérification d’existence préalable avec DROP peut être utilisée comme dans l’exemple suivant :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 following example:

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

Pour la cohérence de codage, il convient d’utiliser ce modèle pour les tables et les tables temporaires.For coding consistency, it is a good practice to use this pattern for both tables and temporary tables. Il est également judicieux d’utiliser DROP TABLE pour supprimer les tables temporaires lorsque vous avez terminé de les utiliser dans votre code.It is also a good idea to use DROP TABLE to remove temporary tables when you have finished with them in your code. Dans le développement de procédure stockée, il est courant de voir les commandes de suppression regroupées ensemble à la fin d’une procédure pour s’assurer que ces objets sont nettoyés.In stored procedure development, it is 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

Modularisation du codeModularizing code

Étant donné que les tables temporaires peuvent être affichées depuis n’importe quel point d’une session utilisateur, cela peut vous aider à modulariser votre code d’application.Since temporary tables can be seen anywhere in a user session, this can be exploited to help you modularize your application code. Par exemple, la procédure stockée suivante génère le langage DDL pour mettre à jour toutes les statistiques dans la base de données par nom de statistique.For example, 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    t1
;
GO

À ce stade, la seule action qui s’est produite est la création d’une procédure stockée qui génère une table temporaire, #stats_ddl, avec des instructions DDL.At this stage, the only action that has occurred is the creation of a stored procedure that generates a temporary table, #stats_ddl, with DDL statements. Cette procédure stockée abandonne la table #stats_ddl si elle existe déjà pour assurer l’absence d’échec en cas d’exécutions multiples dans une session.This stored procedure drops #stats_ddl if it already exists to ensure it does not fail if run more than once within a session. Toutefois, étant donné l’absence de DROP TABLE à la fin de la procédure stockée, lorsque la procédure stockée se termine, elle quitte la table créée afin de pouvoir être lue en dehors de la procédure stockée.However, since there is no DROP TABLE at the end of the stored procedure, when the stored procedure completes, it leaves the created table so that it can be read outside of the stored procedure. Dans SQL Data Warehouse, contrairement à d’autres bases de données SQL, il est possible d’utiliser la table temporaire en dehors de la procédure qui l’a créée.In SQL Data Warehouse, unlike other SQL Server databases, it is possible to use the temporary table outside of the procedure that created it. Les tables temporaires SQL Data Warehouse peuvent être utilisées à n’importe quel point de la session.SQL Data Warehouse temporary tables can be used anywhere inside the session. Cela peut optimiser la facilité de gestion et la modularité du code comme dans l’exemple suivant :This can lead to more modular and manageable code as in the following 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;

Limitations relatives aux tables temporairesTemporary table limitations

SQL Data Warehouse impose quelques restrictions lors de l’implémentation de tables temporaires.SQL Data Warehouse does impose a couple of limitations when implementing temporary tables. Actuellement, seules les tables temporaires de la session sont prises en charge.Currently, only session scoped temporary tables are supported. Les tables temporaires globales ne sont pas prises en charge.Global Temporary Tables are not supported. En outre, vous ne pouvez pas créer de vues sur des tables temporaires.In addition, views cannot be created on temporary tables. Les tables temporaires peuvent être créées uniquement avec une distribution par hachage ou par tourniquet (round robin).Temporary tables can only be created with hash or round robin distribution. La distribution de tables temporaires répliquées n’est pas prise en charge.Replicated temporary table distribution is not supported.

Étapes suivantesNext steps

Pour en savoir plus sur le développement des tables, consultez la Vue d’ensemble de la Table.To learn more about developing tables, see the Table Overview.