Partilhar via


Tabelas temporárias no Synapse SQL

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

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

Tabelas temporárias

As tabelas temporárias são úteis ao processar dados, especialmente durante a transformação em que os resultados intermédios são transitórios. Com o SQL do Synapse, existem tabelas temporárias ao nível da sessão. Só são visíveis para a sessão em que foram criadas. Como tal, são automaticamente removidos quando a sessão termina.

Tabelas temporárias no conjunto de SQL dedicado

No recurso de conjunto de SQL dedicado, as tabelas temporárias oferecem um benefício de desempenho porque os resultados são escritos no armazenamento local e não remoto.

Criar uma tabela temporária

As tabelas temporárias são criadas ao prefixar o nome da tabela com um #. 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
)

As tabelas temporárias também podem ser criadas com uma CTAS abordagem exatamente igual:

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

Nota

CTAS é um comando poderoso e tem a vantagem adicional de ser eficiente na utilização do espaço de registo de transações.

Remover tabelas temporárias

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

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

Para a consistência de codificação, é uma boa prática utilizar este padrão para tabelas e tabelas temporárias. Também é boa ideia utilizar DROP TABLE para remover tabelas temporárias quando terminar de as utilizar.

No desenvolvimento de procedimentos armazenados, é comum ver os comandos de remoção agrupados no final de um procedimento para garantir que estes objetos são limpos.

DROP TABLE #stats_ddl

Modularizar código

As tabelas temporárias podem ser utilizadas em qualquer lugar numa sessão de utilizador. Em seguida, esta capacidade pode ser explorada para ajudá-lo a modularizar o código da aplicação. Para demonstrar, o seguinte procedimento armazenado gera DDL para atualizar todas as estatísticas na base 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 fase, a única ação que ocorreu é a criação de um procedimento armazenado que gera o #stats_ddl tabela temporária. O procedimento armazenado #stats_ddl se já existir. Esta queda garante que não falha se for executada mais do que uma vez numa sessão.

Uma vez que não existe 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.

Ao contrário de outras bases de dados SQL Server, o Synapse SQL permite-lhe utilizar a tabela temporária fora do procedimento que a criou. As tabelas temporárias criadas através do conjunto de SQL dedicado podem ser utilizadas em qualquer lugar dentro da sessão. Como resultado, terá código mais modular e gerí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 conjunto de SQL dedicado tem algumas limitações de implementação para tabelas temporárias:

  • Apenas são suportadas tabelas temporárias no âmbito da sessão. As Tabelas Temporárias Globais não são suportadas.
  • Não é possível criar vistas em tabelas temporárias.
  • Só pode criar as tabelas temporárias com distribuição hash ou round robin. A distribuição temporária da tabela replicada não é suportada.

Tabelas temporárias no conjunto de 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.

Passos seguintes

Para saber mais sobre o desenvolvimento de tabelas, veja o artigo Criar tabelas com os recursos sqL do Synapse .