Tabelas temporárias no conjunto de SQL dedicado no Azure Synapse Analytics
Este artigo contém orientações essenciais para a utilização de tabelas temporárias e realça os princípios das tabelas temporárias ao nível da sessão.
A utilização das informações neste artigo pode ajudá-lo a modularizar o 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 em que os resultados intermédios são transitórios. No conjunto de SQL dedicado, as tabelas temporárias existem ao 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 removidas automaticamente quando essa sessão é fechada.
As tabelas temporárias oferecem um benefício de desempenho porque os respetivos resultados são escritos no armazenamento local e não remoto.
Tabelas temporárias no conjunto de SQL dedicado
No recurso do 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.
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, pode ser utilizada uma verificação de pré-existência simples com um DROP como no exemplo seguinte:
IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
DROP TABLE #stats_ddl
END
Para a consistência da 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 seu código.
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
Uma vez que as tabelas temporárias podem ser vistas em qualquer lugar numa sessão de utilizador, esta capacidade pode ser aproveitada para ajudá-lo a modularizar o código da aplicação.
Por exemplo, 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 uma tabela temporária, #stats_ddl, com instruções DDL.
Este procedimento armazenado remove um existente #stats_ddl para garantir que não falha se for executado mais do que uma vez numa sessão.
No entanto, uma vez que não existe no DROP TABLE final do procedimento armazenado, quando o procedimento armazenado é concluído, deixa a tabela criada para que possa ser lida fora do procedimento armazenado.
No conjunto de SQL dedicado, ao contrário de outras bases de dados SQL Server, é possível utilizar a tabela temporária fora do procedimento que o criou. As tabelas temporárias do conjunto de SQL dedicado podem ser utilizadas em qualquer parte da sessão. Esta funcionalidade pode originar código mais modular e gerível, tal como no exemplo seguinte:
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 impõe algumas limitações ao implementar tabelas temporárias. Atualmente, apenas são suportadas tabelas temporárias no âmbito da sessão. As Tabelas Temporárias Globais não são suportadas.
Além disso, as vistas não podem ser criadas em tabelas temporárias. Só pode criar as tabelas temporárias com distribuição hash ou round robin. A distribuição temporária de tabelas replicada não é suportada.
Passos seguintes
Para saber mais sobre o desenvolvimento de tabelas, veja o artigo Estruturar tabelas com o conjunto de SQL dedicado .