Tabelas temporárias no SQL Data WarehouseTemporary tables in SQL Data Warehouse

Este artigo contém as diretrizes essenciais de como usar as tabelas temporárias e destaca os princípios das tabelas temporárias no nível da sessão.This article contains essential guidance for using temporary tables and highlights the principles of session level temporary tables. Usar as informações neste artigo pode ajudá-lo a modularizar seu código, melhorando a reutilização e a facilidade de manutenção do seu código.Using the information in this article can help you modularize your code, improving both reusability and ease of maintenance of your code.

O que são tabelas temporárias?What are temporary tables?

As tabelas temporárias são úteis durante o processamento de dados - especialmente durante a transformação onde os resultados intermediários são transitórios.Temporary tables are useful when processing data - especially during transformation where the intermediate results are transient. No SQL Data Warehouse, existem tabelas temporárias no nível de sessão.In SQL Data Warehouse, temporary tables exist at the session level. Elas são visíveis apenas para a sessão na qual foram criadas e são descartadas automaticamente quando a sessão faz logoff.They are only visible to the session in which they were created and are automatically dropped when that session logs off. As tabelas temporárias oferecem um benefício de desempenho, pois seus resultados são gravados no local, em vez do armazenamento remoto.Temporary tables offer a performance benefit because their results are written to local rather than remote storage.

Criar uma tabela temporáriaCreate a temporary table

As tabelas temporárias são criadas simplesmente prefixando o nome da tabela com um #.Temporary tables are created by prefixing your table name with a #. Por exemplo: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
)

As tabelas temporárias também podem ser criadas usando CTAS com a mesma abordagem: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]
)
;

Observação

CTAS é um comando potente com a vantagem extra de ser muito eficiente em seu uso do espaço de log das transações.CTAS is a powerful command and has the added advantage of being efficient in its use of transaction log space.

Descartando tabelas temporáriasDropping temporary tables

Quando uma nova sessão é criada, não deve haver nenhuma tabela temporária.When a new session is created, no temporary tables should exist. No entanto, se você estiver chamando o mesmo procedimento armazenado, o que cria um temporário com o mesmo nome, para garantir que suas instruções CREATE TABLE sejam bem-sucedidas, uma simples verificação da existência com DROP pode ser usada como no exemplo abaixo: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

Para a consistência da codificação, é recomendável usar esse padrão para as tabelas e as tabelas temporárias.For coding consistency, it is a good practice to use this pattern for both tables and temporary tables. Também é uma boa prática usar DROP TABLE para remover as tabelas temporárias quando você tiver terminado o trabalho com elas no código.It is also a good idea to use DROP TABLE to remove temporary tables when you have finished with them in your code. No desenvolvimento de procedimento armazenado, é comum ver os comandos de remoção agrupados no fim de um procedimento para garantir que esses objetos sejam limpos.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

Modularizar o códigoModularizing code

Como as tabelas temporárias podem ser vistas em qualquer lugar em uma sessão do usuário, isso pode ser explorado para ajudá-lo a modularizar o código do aplicativo.Since temporary tables can be seen anywhere in a user session, this can be exploited to help you modularize your application code. Por exemplo, o seguinte procedimento armazenado gera DDL para atualizar todas as estatísticas no banco de dados pelo nome da estatística.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

Nesse estágio, a única ação que ocorreu é a criação de um procedimento armazenado que gera uma tabela temporária, #stats_ddl, com instruções 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. Esse procedimento armazenado descartará #stats_ddl se ela já existir para garantir que não falhará se for executada mais de uma vez em uma sessão.This stored procedure drops #stats_ddl if it already exists to ensure it does not fail if run more than once within a session. No entanto, já que não há nenhum DROP TABLE no final do procedimento armazenado, quando o procedimento armazenado for concluído, ele deixará a tabela criada para que possa ser lida de fora do procedimento armazenado.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. No SQL Data Warehouse, ao contrário dos outros bancos de dados do SQL Server, é possível usar a tabela temporária fora do procedimento que a criou.In SQL Data Warehouse, unlike other SQL Server databases, it is possible to use the temporary table outside of the procedure that created it. As tabelas temporárias do SQL Data Warehouse podem ser usadas em qualquer lugar na sessão.SQL Data Warehouse temporary tables can be used anywhere inside the session. Isso pode levar a um código mais modular e gerenciável, como no exemplo abaixo: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;

Limitações da tabela temporáriaTemporary table limitations

O SQL Data Warehouse impõe algumas limitações ao implementar a tabelas temporárias.SQL Data Warehouse does impose a couple of limitations when implementing temporary tables. Atualmente, somente a sessão com o escopo das tabelas temporárias é suportada.Currently, only session scoped temporary tables are supported. Não há suporte para as Tabelas Temporárias Globais.Global Temporary Tables are not supported. E mais, as exibições não podem ser criadas nas tabelas temporárias.In addition, views cannot be created on temporary tables. As tabelas temporárias só podem ser criadas com a distribuição hash ou round robin.Temporary tables can only be created with hash or round robin distribution. Não há suporte para a distribuição de tabela temporária replicada.Replicated temporary table distribution is not supported.

Próximas etapasNext steps

Para saber mais sobre como desenvolver tabelas, consulte a Visão geral da tabela.To learn more about developing tables, see the Table Overview.