T-SQL e a utilização de tabelas temporárias

No decorrer de nossos projetos, há momentos em que a recuperação de dados para um complexo requisito de negócio requer que armazenemos temporariamente um ou mais conjuntos de resultados por um curto período de tempo. Normalmente estas tabelas temporárias são armazenadas no escopo da conexão atual, mas elas também podem precisar estar disponíveis em múltiplas conexões. Neste artigo vamos discutir a utilização de tabelas temporárias no T-SQL e vamos mostrar, através de exemplos, como podemos utilizá-las de uma melhor forma possível.

As tabelas temporárias são usadas com maior frequência para fornecer espaço de trabalho para os resultados intermediários no processamento de dados dentro de um lote ou de um procedimento (uma procedure). As tabelas temporárias incluem dentre outros aspectos, as tabelas temporárias locais, tabelas globais temporárias, tabelas temporárias persistentes (que são prefixadas por TempDB) e as variáveis de tabela (começando com (@)).

Variáveis de tabela

As variáveis de tabela são usadas no âmbito de rotina ou lote em que elas são definidas e foram originalmente criadas para tornar as funções com valor de tabela possível. No entanto, elas são boas para muitos dos usos dos quais são colocados para a tabela temporária tradicional. Elas se comportam como outras variáveis em suas regras de escopo. Uma vez fora do escopo, elas são eliminadas. Estas são muito mais fácil de trabalhar e muito mais seguras e provocam menos recompiles nas rotinas onde elas são usadas do que se estivéssemos a usar tabelas temporárias. Variáveis de tabela requerem menos recursos de bloqueio assim como elas são tipo "privado"para o processo que as criou. Reversões de transação (rollbacks) não as afetam, pois as variáveis de tabela têm alcance limitado e não fazem parte do banco de dados persistente, por isso, elas são úteis para a criação ou o armazenamento de dados que devem sobreviver a reversões, como entradas de registro. A desvantagem no uso das variáveis de tabela é que elas são muitas vezes eliminadas antes que possamos investigar seu conteúdo para a depuração, ou mesmo utilizá-las para experimentar diferentes expressões SQL interativamente.

Algumas coisas que não podemos fazer, por exemplo, são:

• Alterar a definição da tabela após a declaração inicial DECLARE. No SQL Server 2000, uma variável de tabela não pode ser o destino de uma instrução SELECT INTO ou EXEC INSERT (agora consertado);
• Não podemos chamar funções definidas pelo usuário a partir de restrições CHECK, valores padrão e colunas computadas na variável de tabela. As únicas restrições que estamos autorizados além de restrições CHECK são a chave primária, chave única, e NULL/NOT NULL.

Os problemas mais difíceis, porém, vêm com o aumento do tamanho das tabelas, porque não podemos declarar um índice de forma explícita e estatísticas de distribuição, pois não são mantidos sobre eles. O Otimizador de Consultas assume que há apenas uma linha na tabela. Também não podemos gerar planos de consultas paralelos para uma expressão SQL que está modificando o conteúdo da tabela. Para obter parcialmente em torno da restrição de índice, podemos usar restrições para fazer a mesma coisa. Mais importante é a restrição de chave primária que lhe permite impor um índice de cluster, mas restrições exclusivas são úteis para o desempenho. O maior problema com variáveis de tabela é que as estatísticas não são mantidas nas colunas. Isso significa que o otimizador de consulta tem de fazer “uma adivinhação” quanto ao tamanho e distribuição dos dados e se ele recebê-los errado, então vamos ver o mau desempenho em associações. Se isso acontecer, o melhor a se fazer é utilizar as clássicas tabelas temporárias locais. Uma coisa que podemos tentar é adicionar a opção (recompilação) para a afirmação de que envolve a variável tabela de junção com outras tabelas. Ao fazer isso, o SQL Server será capaz de detectar número de linhas na recompilação porque as linhas já terão sido preenchidas. Isso não resolve totalmente o problema uma vez que o otimizador ainda não tem estatísticas de distribuição e pode, produzir um plano ruim. Neste exemplo, a junção foi reduzida no tempo em três quartos simples, adicionando a opção (RECOMPILAR). Vejamos como isso fica num exemplo de acordo com a Listagem 1.

SET nocount ON 
DECLARE @FirstTable TABLE (RandomInteger INT)
DECLARE @SecondTable TABLE (RandomInteger INT)
DECLARE @WhenWeStarted DATETIME
DECLARE @ii INT
 
BEGIN TRANSACTION
SET @ii = 0
WHILE @ii < 10000
  BEGIN
    INSERT  INTO @FirstTable
    VALUES  (RAND() * 10000)
    SET @ii = @ii + 1
  END
SET @ii = 0
WHILE @ii < 100000
  BEGIN
    INSERT  INTO @SecondTable
    VALUES  (RAND() * 10000)
    SET @ii = @ii + 1
  END
COMMIT TRANSACTION
SELECT  @WhenWeStarted = GETDATE()
SET STATISTICS PROFILE ON
SELECT  COUNT(*)
FROM    @FirstTable first
        INNER JOIN @SecondTable second
        ON first.RandomInteger = second.RandomInteger OPTION (RECOMPILE)
SET STATISTICS PROFILE OFF
SELECT  'That took '
    + CONVERT(VARCHAR(8), DATEDIFF(ms, @WhenWeStarted, GETDATE()))
    + 'ms'
Go

Agora, se podemos fazer o que vai para as tabelas exclusivas, podemos usar uma restrição de chave primária nessas tabelas. Isso permite que o otimizador use um índice agrupado em vez de fazer uma varredura na tabela, deixando assim, o tempo de execução muito mais rápido.

Parâmetros com valor de tabela

O parâmetro com valor de tabela (TVP) é um tipo especial de variável de tabela que estende a sua utilização. Quando as variáveis da tabela são passadas como parâmetros, a tabela é materializada na base de dados do sistema tempdb como uma variável de tabela e passados por referência, um ponteiro para a tabela no tempdb.

Parâmetros com valor de tabela têm sido usados desde o SQL Server 2008 para enviar várias linhas de dados para uma rotina de Transact-SQL ou a um lote via Sp_executesql. Seu valor especial para o programador é que eles podem ser usados no código T-SQL, bem como na aplicação do cliente, de modo que eles são bons para o envio de tabelas do cliente para o servidor. No T-SQL, podemos declarar variáveis com valor de tabela, inserir dados neles, e passar essas variáveis como parâmetros com valor de tabela para Stored procedures e functions. Sua utilidade mais geral é limitada pelo fato de que eles só são passados como read-only. Não podemos, no entanto, fazer UPDATE, DELETE ou INSERT em um parâmetro com valor de tabela no corpo de uma rotina. Precisamos criar um tipo de tabela definido pelo usuário e definir uma estrutura de tabela para usá-los. De acordo com a Listagem 2, apresentamos um exemplo simples de uso em T-SQL.

CREATE TYPE Names AS TABLE
(Name VARCHAR(10));
GO
 /* em seguida, criamos a procedure para receber os dados para o 
 parâmetro e então fazemos a seleção dos dados */
CREATE PROCEDURE ChooseAName
  @CandidateNames Names READONLY
AS
DECLARE @candidates TABLE (NAME VARCHAR(10),
                           theOrder UNIQUEIDENTIFIER)
INSERT  INTO @candidates (name, theorder)
        SELECT  name, NEWID()
        FROM    @CandidateNames
SELECT TOP 1
        NAME
FROM    @Candidates
ORDER BY theOrder
GO
 
/*  declarando uma variável que referencia o tipo para nossas listas. */
DECLARE @MyFavouriteCowName AS Names ; 
/* adicionando dados na variável de tabela */
INSERT  INTO @MyFavouriteCowName (Name)
SELECT 'Bossy' UNION SELECT 'Bessy' UNION SELECT 'petal' UNION SELECT 
'Daisy' UNION SELECT 'Lulu' UNION SELECT 'Buttercup' UNION SELECT 
'Bertha' UNION SELECT 'Bubba' UNION SELECT 'Beauregard' UNION SELECT 
'Brunhilde' UNION SELECT 'Lore' UNION SELECT 'Lotte' UNION SELECT 'Rosa' 
UNION SELECT 'Thilde' UNION SELECT 'Lisa' UNION SELECT 'Peppo' 
UNION SELECT 'Maxi' UNION SELECT 'Moriz' UNION SELECT 'Marla'
 
/* passando a tabela com a lista de nomes para uma stored procedure tradicional. */
EXEC chooseAName @MyFavouriteCowName
GO

Com variáveis de tabela, o parâmetro com valor de tabela deixa de existir, uma vez que está fora de alcance, mas a definição do tipo permanece até que seja explicitamente abandonada. Como variáveis de tabela não adquirem bloqueios quando os dados estão sendo preenchidos a partir de um cliente, e as estatísticas não são mantidas em colunas de parâmetros com valor de tabela. Não podemos usar um parâmetro com valor de tabela como alvo de um SELECT INTO ou INSERT EXEC. Como seria de esperar, um parâmetro com valor de tabela pode estar na cláusula de SELECT INTO ou na cadeia de caracteres INSERT EXEC ou mesmo numa stored procedure.

Antes de passarmos para descrever as tabelas temporárias mais tradicionais e seu uso, vamos precisar nos aprofundar primeiro no lugar onde as tabelas temporárias são armazenadas, que é a Tempdb.

Um pouco sobre a tabela TempDB

As tabelas temporárias e variáveis de tabela são criadas no banco de dados tempdb, que é realmente apenas mais um banco de dados com recuperação simples. Com TempDB, apenas logging "mínimo" suficiente é feito para permitir a reversão, e outras sutilezas ACID. A diferença especial de tempdb é que todos os objetos, como tabelas são esvaziados na inicialização. Porque TempDB sempre usa o modelo de recuperação simples, a transação foi concluída, são então apagados do registro de log no próximo checkpoint da TempDB, e apenas as transações ao vivo são retidas. Isso tudo significa que as tabelas temporárias se comportam como qualquer outro tipo de tabela base em que eles são registrados e armazenados. Na prática, as tabelas temporárias são susceptíveis de permanecer em cache na memória, mas somente se elas são usadas com frequência. A tempDB opera um sistema chamado de reutilização temporária de objeto, que irá armazenar em cache uma parte dos objetos temporários, se houver memória suficiente.

Como as tabelas temporárias são armazenadas como tabelas de base, há uma ou duas coisas que precisamos ser cautelosos aqui. Devemos, por exemplo, ter a permissão CREATE TABLE em tempdb, a fim de criar uma tabela normal. Para poupar o trabalho, este é atribuído por padrão para o papel do DBO (proprietário db), mas pode ser necessário fazê-lo explicitamente para os usuários que não são DBO. Todos os usuários têm permissões para criar tabelas temporárias locais ou globais em tempdb porque este é atribuído a eles por meio do contexto de segurança do usuário GUEST.

A tabela temporária clássica vem de duas formas, a Global, prefixada por '##', e a tabela temporária local, cujo nome é prefixado com '#'. As tabelas temporárias locais são mais restritas do que as tabelas temporárias globais. Não podemos, por exemplo, criar pontos de views sobre elas. É um pouco complicado de descobrir qual processo, sessão ou procedimento foi criado. Vamos dar-lhe um pouco de ajuda com isso mais tarde. Mais importante, eles são mais seguros do que uma tabela temporária global.

Outra esquisitice da tabela temporária local (e o procedimento armazenado temporário local) é que ele tem um nome diferente nos metadados ao que damos em nossa rotina ou lote. Se a mesma rotina é executada simultaneamente por vários processos, o motor de base de dados tem de ser capaz de distinguir entre as tabelas temporárias locais com nomes idênticos criados pelos diferentes processos. Ele faz isso através da adição de uma sequência numérica para cada nome de tabela temporária local preenchidas por caracteres de sublinhado. Embora especifiquemos o nome abreviado como #MyTempTable, o que está realmente armazenado na tempdb é composta do nome da tabela especificada na instrução CREATE TABLE e o sufixo. Devido a este sufixo, nomes de tabelas temporárias locais devem ter 116 caracteres ou menos.

Não podemos usar os tipos de dados definidos pelo usuário em tabelas temporárias, a menos que os tipos de dados existam em tempdb, isto é, a menos que os tipos de dados tenham sido criados explicitamente.

Tabelas de usuários em tempdb

Em uso normal, iremos criar tabelas temporárias ou variáveis de tabela, sem pensar muito profundamente sobre isso. No entanto, é interessante, porém, que a TempDB está lá para qualquer tipo de atividade sandbox. Podemos criar tabelas comuns de base, pontos de vista, ou qualquer outra coisa que venhamos querer. Podemos criar esquemas, stored procedures e assim por diante.

Ao contrário da tabela temporária global, temos que fazer todo o nosso próprio serviço de limpeza nele. O mesmo é verdade para as rotinas. A vantagem de fazer isso é que qualquer tratamento que façamos, utiliza de recuperação simples do TempDB de modo que, se não conseguirmos limpar, o SQL Server atua na próxima inicialização, embora este poderia ser um tempo muito longo. A próxima etapa é ter o que chamamos de uma tabela 'persistente temporária". Nesta tabela os dados em si são voláteis quando o servidor for reiniciado, mas a própria tabela persiste. Provavelmente, a maneira mais comum para criar uma tabela temporária persistente é recriar na inicialização uma tabela temporária global. Isso pode ser feito automaticamente quando todos os bancos de dados são recuperados e "A recuperação está concluída" mensagem é registrada. Mesmo que esta seja uma "global temporária", ela não é excluída quando todas as conexões usando elas desaparecem, porque o processo que corre nunca desaparece. Sem dúvida, é melhor para criar este tipo de tabela de trabalho no banco de dados que usá-lo, no entanto, se estivermos usando a recuperação total, o trabalho temporário permanecerá no registro. Podemos criar essas tabelas 'persistentes' na inicialização, definindo um procedimento armazenado no master que cria a tabela temporária global. Apresentamos essa criação de acordo com a Listagem 3.

USE master
go
CREATE PROCEDURE createMyGlobalTables  AS
   CREATE TABLE ##globalTemporary1
      (-- (insira a DDL aqui)
   CREATE TABLE ##globalTemporary2
      (-- (insira a DDL aqui)
--e assim por diante….
   CREATE TABLE ##globalTemporaryn
      (---- (insira a DDL aqui) 
Go
EXEC sp_procoption 'createMyGlobalTables', 'startup', 'true'

Por que usar este tipo de tabela híbrida? Existem, por exemplo, um número de técnicas para a passagem de quadros entre procedimentos através de tabelas 'persistentes' de uma maneira multiprocesso, de forma a fazer uma série de processamentos de dados. Estes são referidos numa tabela com a chave do processo. Eles vão inicialmente levantar as sobrancelhas de qualquer DBA experiente, mas eles são uma solução eficaz e segura para um problema perene, quando são feitos corretamente.

Assim como tabelas temporárias, há também uma série de tipos de tabelas que não estão diretamente relacionadas com tabelas de base, tais como tabelas derivadas. Alguns deles são tão fugazes que eles são melhor vistos como efêmeros, em vez de temporários.

Tabelas temporárias locais

A partir daqui, daremos uma analisada em diferentes exemplos de uso das tabelas temporárias para que possamos ter uma melhor noção de como usá-las. No decorrer deste artigo estaremos utilizando a base de dados do banco de dados AdventureWorks2012_database. Se vocês quiserem acompanhar e executar os exemplos que iremos apresentar neste artigo, vocês podem baixar o banco de dados AdventureWorks2012_database a partir do link disponibilizado na MSFTDBProdSamples.

Seguindo agora para o nosso primeiro exemplo, iremos criar, preencher e selecionar dados de uma tabela temporária. Assim como apresentado pela Listagem 4.

SET NOCOUNT ON;
CREATE TABLE #Person (
    BusinessEntityId int,
    Title                    nvarchar(8),
    FirstName       nvarchar(50),
    LastName        nvarchar(50));
 
INSERT INTO #Person (BusinessEntityID, Title, FirstName, LastName) 
    SELECT TOP 10 BusinessEntityID, Title, FirstName, LastName
    FROM AdventureWOrks2012_database.Person.Person;
SELECT * FROM #Person;
DROP TABLE #Person;

No exemplo que apresentamos através da Listagem 4, primeiramente criamos uma tabela temporária local chamada #Person. Vocês podem então dizer que isso é uma tabela temporária local, devido ao nome da tabela começar com um único caractere de sinal de número (#). Em seguida povoamos a tabela temporária local recém-criada usando uma instrução INSERT que selecionou algumas colunas da tabela de Person.Person no banco de dados AdventureWorks2012. Então, a partir disso, usamos uma instrução SELECT para mostrar as linhas que foram inseridas na nossa tabela temporária local #Person. Finalmente, apagamos a nossa tabela temporária local. Como vocês puderam observar, a criação de uma tabela temporária local não é muito diferente do que a criação de qualquer outra tabela.

Seguindo para o nosso segundo exemplo, vamos observar então o código presente na Listagem 5.

SET NOCOUNT ON;
CREATE TABLE ##SalesOrderHeader (
    SalesOrderId int,
    SalesOrderDate DateTime,
    CustomerId int);
 
INSERT INTO ##SalesOrderHeader (SalesOrderId, SalesOrderDate, CustomerId)
    SELECT SOH.SalesOrderID, SOH.OrderDate, C.CustomerID
    FROM AdventureWorks2012.Sales.SalesOrderHeader SOH
    LEFT OUTER JOIN AdventureWorks2012.Sales.Customer C
    ON  SOH.CustomerID = C.CustomerID;
 
SELECT TOP 10 * FROM ##SalesOrderHeader;
DROP TABLE ##SalesOrderHeader;

Se vocês examinarem o código apresentado na Listagem 5, vocês verão que criamos aqui, usamos e descartamos uma tabela chamada ##SalesOrderHeader que é temporária e global assim como fizemos com a tabela temporária local apresentada na Listagem 4. A única diferença entre elas é que a tabela criada na Listagem 5 começa com dois sinais (##), em vez de um. Fazendo uso do par de ## no nome da tabela SalesOrderHeader, dizemos ao SQL Server que esta tabela é uma tabela temporária global.

O código presente na Listagem 4 e na Listagem 5 não demonstram bem a diferença de definição do âmbito destes dois tipos de tabelas temporárias. Como já afirmamos o escopo da tabela temporária local na Listagem 4 é a sessão e o escopo da tabela temporária global na Listagem 4 são todas as sessões. Deixe-nos passar por um exemplo que vai ajudar a mostrar as diferenças de escopo destes dois tipos de tabelas temporárias de uma forma mais significativa.

O teste que iremos executar irá criar três sessões diferentes. Cada sessão será executada uma série diferente de comandos T-SQL. Iremos estar utilizando a janela de consulta no SQL Server Management Studio para cada uma dessas sessões que iremos utilizar.

Para iniciarmos nossos testes, abriremos então três janelas de consulta diferentes no SQL Server Management Studio. Anotaremos o ID da sessão em cada uma das janelas. Uma maneira que podemos fazer isso é executando o comando presente na Listagem 6 em cada uma das janelas de consulta, como segue abaixo.

SELECT @@SPID;

O próximo passo do nosso teste é criarmos uma tabela temporária local em uma janela de consulta e uma tabela temporária global em outra janela de consulta. Na janela de consulta com o número de ID de sessão menor voltou quando executamos o código da Listagem 6, executamos então o código presente na Listagem 7.

CREATE TABLE #Local (TableType varchar(10));
  INSERT INTO #Local values ('Local');

O código presente na Listagem 7 cria então a nossa tabela temporária local. Para o próximo passo que iremos executar, será utilizando o código presente na Listagem 8 na janela de consulta que tem o segundo menor número de ID de sessão.

CREATE TABLE ##Global (TableType varchar(10));
INSERT INTO ##Global values ('Global');

Com o código apresentado na Listagem 8 criamos então uma tabela temporária global. Para a última etapa do nosso teste, executaremos o código presente na Listagem 9, na nossa última janela de consulta aberta que por conseguinte, tem a maior id da sessão.

SET NOCOUNT ON;
SELECT * FROM ##Global;
GO
SELECT * FROM #Local;

O código apresentado na Listagem 9 faz referência as tabelas locais e globais que criamos nas duas primeiras sessões apresentadas nas janelas de execução anteriores. Quando executamos o código da Listagem 9 e exibindo a nossa saída em modo texto, recebemos a seguinte saída:

TableType
----------
Global
Msg 208, Level 16, State 0, Line 3
Invalid object name '#Local'.

Ao analisarmos a saída apresentada, podemos ver que podíamos fazer referência a tabela temporária global nesta sessão, mas não a tabela temporária local. Isso demonstra como as tabelas temporárias locais criadas em uma sessão estão fora do escopo em outra sessão, e como as tabelas temporárias globais estão disponíveis em sessões que não criarmos a tabela temporária global. Além disso, se fecharmos as duas primeiras sessões e, em seguida, tentarmos executar o código na Listagem 7, veremos que a tabela global não está mais disponível.

Uma outra situação em que uma tabela temporária local está fora do escopo é quando tivermos chamadas de procedimento aninhado armazenado. Na realidade, as tabelas temporárias são visíveis apenas na sessão atual, quando o nível de aninhamento é o mesmo ou maior que o nível de aninhamento em que a tabela temporária foi criada. Deixe-nos demonstrar isso executando o código presente na Listagem 10.

SET NOCOUNT ON;
GO
CREATE PROC MyProc 
AS
CREATE TABLE #Level1 (Level int);
INSERT #Level1 VALUES (1);
SELECT @@NESTLEVEL as Level, @@SPID as SPID
SELECT * FROM #Level0;
SELECT * FROM #Level1;
GO
CREATE TABLE #Level0 (Level int);
INSERT #Level0 VALUES (0);
SELECT @@NESTLEVEL as Level, @@SPID as SPID
EXEC MyProc;
SELECT * FROM #Level0;
SELECT * FROM #Level1;

Quando executarmos esse código, receberemos a saída associada a Listagem 10:

Level       SPID
----------- ------
1           55
Level
-----------
0
Level
-----------
1
Level       SPID
----------- ------
0           55
Level
-----------
0
Msg 208, Level 16, State 0, Line 6
Invalid object name '#Level1'.

Ao analisarmos o código apresentado e de saída associada a Listagem 10, podemos ver que quando uma tabela temporária local é criada no nível de aninhamento superior não está disponível para um nível de aninhamento mais baixo, mesmo quando eles são criados na mesma sessão. Na Listagem 10 criamos e preenchemos uma tabela temporária chamada #level0 em com nível 0, então chamamos um procedimento chamado MyProc armazenado que é executado em nível de aninhamento 1. Nesse procedimento armazenado criamos e preenchemos uma tabela temporária chamada #Nível 1. Nesse mesmo procedimento armazenado exibimos o nível de ninho e SPID (Session ID), e os dados selecionados das duas tabelas temporárias locais (# e # level0 Nível1) que foram criadas por nossa sessão. Fomos bem sucedidos em consultar ambas as tabelas temporárias locais em MyProc porque a tabela #level0 foi criada em um nível mais baixo do ninho, e a tabela #Nível 1 foi criado ao mesmo nível de aninhamento. Mas quando nossa stored procedure MyProc termina, o SQL Server automaticamente exclui da tabela #Level1 temporária. Isso fez com que a última instrução SELECT falhasse com um erro de "nome de objeto inválido", porque estávamos tentando fazer referência a tabela #Level1 temporária que foi criada em um nível mais elevado ninho então meu nível ninho atual.

O que podemos fazer para melhorar nesses casos?

Aqui estão algumas das melhores práticas que devemos considerar ao criarmos tabelas temporárias:

• Antes de criarmos uma tabela temporária, devemos verificar que não podemos criar uma consulta baseada em um conjunto que não requer a criação de uma tabela temporária.
• Não devemos apagar explicitamente tabelas temporárias, logo que eles não sejam mais necessários.

Tenham sempre em mente que o mau uso de tabelas temporárias, ou com muito tempo de vida, pode ter efeitos sobre outros processos, até mesmo em outros bancos de dados no servidor. Como podemos observar no decorrer deste artigo, as tabelas temporárias locais são uma ótima maneira de armazenar um conjunto de resultados por um curto período de tempo para que possamos usá-la num processamento adicional dentro da mesma sessão. Mas tenham em mente que as tabelas temporárias tomam espaço na tabela tempdb e, que portanto, devemos deixá-las assim que elas não são mais necessárias. Se precisarmos de tabelas temporárias para estar disponível por várias sessões, em seguida, teremos que criar uma tabela temporária global.