Tabelas temporárias no Synapse SQL

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

Tanto o pool SQL dedicado quanto os recursos do pool SQL sem servidor podem utilizar tabelas temporárias. O pool SQL sem servidor tem limitações que são discutidas no final deste artigo.

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. Com o Synapse SQL, existem tabelas temporárias no nível da sessão. Eles só são visíveis na sessão em que foram criados. Como tal, são automaticamente descartados quando a sessão termina.

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

Observação

CTAS é um comando poderoso e tem a vantagem adicional de ser eficiente no uso do espaço de log de transações.

Eliminar tabelas temporárias

Quando uma nova sessão é criada, não devem existir tabelas temporárias. No entanto, se estiver a chamar o mesmo procedimento armazenado que cria um temporário com o mesmo nome, para garantir que as suas CREATE TABLE instruções sejam bem-sucedidas, utilize uma verificação simples de pré-existência com DROP:

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 terminar de usá-las.

No desenvolvimento de procedimentos armazenados, é comum ver os comandos drop agrupados no final de um procedimento para garantir a limpeza desses objetos.

DROP TABLE #stats_ddl

Modularizar código

As tabelas temporárias podem ser usadas em qualquer lugar em uma sessão de usuário. Esse recurso pode ser explorado para ajudá-lo a modularizar o código do aplicativo. Para demonstrar, o seguinte procedimento armazenado gera DDL para atualizar todas as estatísticas no banco de dados com base no nome da 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 a tabela temporária #stats_ddl. O procedimento armazenado descarta #stats_ddl se já existir. Essa queda garante que ele não falhe se executado mais de uma vez em uma sessão.

Como não há um DROP TABLE no final do procedimento armazenado, quando o procedimento armazenado é concluído, a tabela criada permanece e pode ser lida fora do procedimento armazenado.

Em contraste com outros bancos de dados do SQL Server, o Synapse SQL permite que você use a tabela temporária fora do procedimento que a criou. As tabelas temporárias criadas por meio do pool SQL dedicado podem ser usadas em qualquer lugar dentro da sessão. Como resultado, você terá um código mais modular e gerenciável, conforme demonstrado no exemplo abaixo:

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 tem algumas limitações de implementação para tabelas temporárias:

Somente tabelas temporárias com escopo de sessão são suportadas. Não há suporte para Tabelas Temporárias Globais.
Não é possível criar vistas em tabelas temporárias.
As tabelas temporárias apenas podem ser criadas com distribuição hash ou round robin. Não há suporte para distribuição de tabela temporária replicada.

Tabelas temporárias no pool SQL sem servidor

As tabelas temporárias no conjunto de SQL sem servidor são suportadas, mas a utilização é limitada. Não podem ser utilizadas em consultas destinadas a ficheiros.

Por exemplo, não pode associar uma tabela temporária a dados de ficheiros no armazenamento. O número de tabelas temporárias está limitado a 100 e o tamanho total está limitado a 100 MB.

Próximos passos

Para saber mais sobre o desenvolvimento de tabelas, consulte o artigo Projetando tabelas usando os recursos do Synapse SQL .

Feedback

Esta página foi útil?

Last updated on 2025-04-06

Partilhar via

Tabelas temporárias no Synapse SQL