Tabelas temporárias no pool SQL dedicado no Azure Synapse Analytics

Este artigo contém orientações essenciais para o uso de tabelas temporárias e destaca os princípios das tabelas temporárias no nível da sessão.

Usar as informações deste artigo pode ajudá-lo a modularizar seu código, melhorando a reutilização e a facilidade de manutenção.

O que são tabelas temporárias?

As tabelas temporárias são úteis no processamento de dados, especialmente durante a transformação, onde os resultados intermediários são transitórios. No pool SQL dedicado, existem tabelas temporárias no nível da sessão.

As tabelas temporárias só são visíveis para a sessão em que foram criadas e são automaticamente descartadas quando essa sessão é encerrada.

As tabelas temporárias oferecem um benefício de desempenho porque seus resultados são gravados no armazenamento local e não no remoto.

Tabelas temporárias no pool SQL dedicado

No recurso de pool SQL dedicado, as tabelas temporárias oferecem um benefício de desempenho porque seus resultados são gravados no armazenamento local em vez de remoto.

Criar uma tabela temporária

As tabelas temporárias são criadas prefixando o nome da tabela com #. Por exemplo:

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
)

Tabelas temporárias podem também ser criadas com CTAS utilizando exatamente a mesma abordagem.

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

Eliminar tabelas temporárias

Quando uma nova sessão é criada, não devem existir tabelas temporárias.

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

Para consistência de codificação, é uma boa prática usar esse padrão para tabelas e tabelas temporárias. Também é uma boa ideia usar DROP TABLE para remover tabelas temporárias quando você terminar de usá-las em seu código.

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

Modularizar código

Como as tabelas temporárias podem ser vistas em qualquer lugar em uma sessão de usuário, esse recurso pode ser aproveitado para ajudá-lo a modularizar o código do aplicativo.

Por exemplo, o procedimento armazenado a seguir gera DDL para atualizar todas as estatísticas no banco de dados por nome de estatística:

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

Nesta etapa, a única ação que ocorreu é a criação de um procedimento armazenado que gera uma tabela temporária, #stats_ddlcom instruções DDL.

Esse procedimento armazenado descarta um existente #stats_ddl para garantir que ele não falhe se executado mais de uma vez em uma sessão.

No entanto, como não há DROP TABLE no final do procedimento armazenado, quando este é concluído, deixa a tabela criada para que possa ser lida fora do procedimento.

No pool SQL dedicado, ao contrário de outros bancos de dados do SQL Server, é possível usar a tabela temporária fora do procedimento que a criou. As tabelas temporárias dedicadas do pool SQL podem ser usadas em qualquer lugar dentro da sessão. Esse recurso pode levar a um código mais modular e gerenciável, como no exemplo a seguir:

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 temporárias da tabela

O pool SQL dedicado impõe algumas limitações ao implementar tabelas temporárias. Atualmente, apenas tabelas temporárias com escopo de sessão são suportadas. Não há suporte para Tabelas Temporárias Globais.

Além disso, as vistas não podem ser criadas em tabelas temporárias. Temporary tables can only be created with hash or round robin distribution. Não há suporte para distribuição de tabela temporária replicada.

Próximos passos

Para saber mais sobre como desenvolver tabelas, consulte o artigo Criando tabelas usando pool SQL dedicado .