Tables temporaires dans le pool SQL dédiéTemporary tables in dedicated SQL pool

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, améliorant ainsi 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.

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. Dans le pool SQL dédié, les tables temporaires existent au niveau de la session.In dedicated SQL pool, temporary tables exist at the session level.

Elles sont uniquement visibles pour la session dans laquelle elles ont été créées et sont automatiquement supprimées lorsque cette session se déconnecte.Temporary tables 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.

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. Avec un pool SQL dédié, les tables temporaires existent au niveau de la session.With dedicated SQL pool, temporary tables exist at the session level. Elles sont visibles uniquement dans la session dans laquelle elles ont été créées.They're only visible to the session in which they were created. Ainsi, elles sont automatiquement supprimées à la fermeture de cette session.As such, they're automatically dropped when that session logs off.

Tables temporaires dans le pool SQL dédiéTemporary tables in dedicated SQL pool

Dans la ressource de pool SQL dédié, les tables temporaires offrent un gain de performances, car leurs résultats sont écrits en local et non dans un stockage distant.In the dedicated SQL pool resource, 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.

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 :If you're 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's 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 fini de les utiliser dans votre code.It's also a good idea to use DROP TABLE to remove temporary tables when you've 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 à la fin d’une procédure pour s’assurer que ces objets sont nettoyés.In stored procedure development, it's 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 visibles à n’importe quel point d’une session utilisateur, cette fonctionnalité peut vous aider à modulariser le code de votre application.Since temporary tables can be seen anywhere in a user session, this capability can be leveraged 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    #stats_ddl
;
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 une table #stats_ddl existante pour s’assurer qu’elle n’échoue pas en cas d’exécutions multiples dans une session.This stored procedure drops an existing #stats_ddl to ensure it doesn't 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 le pool SQL dédié, contrairement à d’autres bases de données SQL Server, il est possible d’utiliser la table temporaire en dehors de la procédure qui l’a créée.In dedicated SQL pool, unlike other SQL Server databases, it's possible to use the temporary table outside of the procedure that created it. Les tables temporaires du pool SQL dédié sont utilisables à n’importe quel point de la session.Dedicated SQL pool temporary tables can be used anywhere inside the session. Cette fonctionnalité peut optimiser la facilité de gestion et la modularité du code comme dans l’exemple suivant :This feature 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

Le pool SQL dédié impose quelques restrictions lors de l’implémentation de tables temporaires.Dedicated SQL pool 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 aren't supported.

Par ailleurs, il n’est pas possible de créer des vues sur des tables temporaires.Also, views can't 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 isn't supported.

Étapes suivantesNext steps

Pour en savoir plus sur le développement de tables, consultez l’article Conception de tables à l’aide d’un pool SQL dédié.To learn more about developing tables, see the Designing tables using dedicated SQL pool article.