Dicas T-SQL – Tabelas Temporárias – Parte 1 – Tabelas Locais

Fala #galera,

Vou iniciar uma pequena série de posts para falar sobre Tabelas Temporárias.

Nesse primeiro post vou falar sobre Tabelas Temporárias Locais. #gogogo

Dicas T-SQL – Tabelas Temporárias – Parte 1 – Tabelas Locais

Dicas T-SQL – Tabelas Temporárias – Parte 2 – Tabelas Globais

Dicas T-SQL – Tabelas Temporárias – Parte 3 – Tabelas Variáveis


Tabelas Temporárias:

Em alguns momentos, nós precisamos armazenar alguns dados apenas de forma temporária ou que seja visível apenas para a sessão atual. Nesses casos, podemos utilizar as tabelas temporárias ao invés das tabelas convencionais ou tabelas permanentes.

Segue abaixo alguns cenários úteis:

  • Ao fazer um loop / cursor, podemos utilizar as tabelas temporárias para armazenar os resultados dos processamentos de cada iteração;
  • Se você precisa fazer uma determinada consulta várias vezes para utilizar o mesmo resultado (por exemplo, as vendas do mês atual) e alterar apenas alguns filtros depois, você poderia salvar esse resultado em uma tabela temporária e depois fazer os filtros nessa nova tabela. Se essa consulta for demorada (alguns segundos ou minutos), podemos ter também um ganho de performance, pois iremos executar ela apenas uma vez e depois o resultado vai estar salvo na tabela temporária (mais rápida).
  • Em alguns Casos de Tuning, eu particularmente gosto de fazer um filtro em uma consulta utilizando menos colunas no SELECT e guardando o ID da PRIMARY KEY (UNIQUE) em uma tabela temporária para depois fazer um JOIN com a tabela original e buscar as outras colunas, pois dessa forma eu consigo utilizar um índice de uma forma mais eficiente (index seek). No exemplo abaixo, a query original retorna 50 colunas no SELECT e eu só precisava da coluna da data para fazer o filtro no índice. Com isso, eu vou fazer primeiro o filtro por data e salvar em uma tabela temporária (aqui só precisei das colunas Id_Venda e Dt_Venda) e depois faço um JOIN com a tabela original pelo Id_Venda que é UNIQUE. Essa Técnica de Tuning é muito útil no meu dia a dia e já perdi as contas de quantos casos já resolvi dessa forma e acho bem simples também. =)
  • Um outro exemplo seria utilizar tabelas temporárias quando o usuário não tiver permissão para criar tabelas permanentes na database;
  • Entre outros… =)
  • Observação Importante:

As Tabelas Temporárias são diferentes das Tabelas Temporais (foi introduzida no SQL Server 2016). São conceitos distintos e nesse momento não vou falar sobre Tabelas Temporais OK.

No SQL Server, podemos utilizar três tipos de tabelas temporárias:

  • Tabelas temporárias locais;
  • Tabelas temporárias globais;
  • Tabelas variáveis.

Vou abordar cada um desses tipos de tabelas em um post diferente.

Todos esses três tipos irão criar as tabelas temporárias na database “tempdb”. Portanto, tome cuidado com a utilização das tabelas temporárias, pois se elas forem muito grandes, podemos ter um crescimento da database “tempdb”. Por isso é importante monitorar também o tamanho e crescimento dessa database. Além disso, fique de olho na quantidade de requisições nessa database, pois podemos ter um gargalo se ela estiver sendo muito utilizada. #ficaadica

Agora vamos iniciar falando sobre as Tabelas Temporárias Locais! #gogogo


Tabelas Temporárias Locais

“Mas Luiz, como eu faço pra criar uma Tabela Temporária Local? É muito difícil?”

Pelo contrário, é muito simples! É a mesma sintaxe de criação de uma tabela permanente e a diferença será apenas no nome da tabela que irá iniciar sempre com o prefixo “#”.

Segue abaixo uma comparação da criação das tabelas:

Agora vamos inserir alguns dados e fazer um SELECT nas tabelas.

Repare que você vai utilizar a tabela temporária normalmente como se estivesse operando com uma tabela permanente. A sintaxe será a mesma também no SELECT, INSERT, UPDATE e DELETE. =)

Lembra que eu falei que as tabelas temporárias são criadas na database “tempdb”? Agora repare que no SELECT nós não precisamos especificar o nome da database antes da tabela temporária.

O curioso é que mesmo especificando as databases, o resultado será o mesmo, pois o SQL Server identifica que se trata de uma tabela temporária. =)


Definição e Nomenclatura da Tabela Temporária:

Se você quiser ver a definição da tabela temporária, você pode utilizar a procedure “sp_help” ou o atalho ALT + F1. Segue um post como referência:

https://luizlima.net/dicas-ssms-retornando-informacoes-sobre-uma-tabela/

Contudo, se você executar em uma database diferente da “tempdb” será gerado um erro informando que a tabela não existe!

Msg 15009, Level 16, State 1, Procedure sp_help, Line 79 [Batch Start Line 31]

The object ‘#TABELA_TESTE_TEMPORARIA’ does not exist in database ‘Traces’ or is invalid for this operation.

Portanto, lembre-se de estar conectado na database “tempdb” antes de executar os comandos.

“Uai Luiz, mas porque o nome da tabela temporária está diferente no resultado??? Que bruxaria é essa cara???”

Por baixo dos panos, o SQL Server precisa gerar um nome único para a tabela temporária (ele adiciona um sufixo ao nome da tabela), pois ela deve ser única para cada sessão. Mas você não precisa se preocupar com isso!

Aqui vou mostrar que é possível fazer o SELECT utilizando os dois nomes, mas quero que finja que você não sabe disso depois OK. Recomendo fortemente que utilize apenas com o nome normal da tabela temporária beleza!

Contudo, se você executar esse script em uma nova sessão, será gerado o erro abaixo (conforme era esperado!):

Msg 208, Level 16, State 0, Line 6

Invalid object name ‘#TABELA_TESTE_TEMPORARIA’.

Msg 208, Level 16, State 0, Line 11

Invalid object name ‘#TABELA_TESTE_TEMPORARIA____________________________________________________________________________________________000000000026’.


Escopo de uma Tabela Temporária Local:

O escopo de uma Tabela Temporária Local é apenas dentro da sessão em que ela for criada e também nos níveis posteriores da CALL STACK (procedures, triggers e batches dinâmicos) que forem chamados a partir dessa sessão.

Uma tabela temporária local é destruída automaticamente pelo SQL Server quando o seu nível de criação for removido da CALL STACK, pois ela não terá mais escopo para ser executada.

Por exemplo, se você criar uma tabela temporária em uma sessão, ela só será excluída se você fizer um DROP TABLE explicitamente ou quando essa sessão for finalizada.

Vamos ver alguns exemplos para fixar esses conceitos. xD

  • Exemplo 1:

Nesse exemplo, criei uma procedure que vai fazer um SELECT na tabela temporária local que criamos anteriormente. Aqui devemos utilizar a mesma sessão, caso contrário a tabela não será encontrada.

Como podemos ver, a procedure foi executada normalmente! =)

  • Exemplo 2:

Agora, vamos criar uma outra tabela temporária local, mas dessa vez dentro de uma outra procedure.

Como podemos ver, a procedure executa normalmente e retorna o resultado. Entretanto, se você tentar fazer um SELECT nessa tabela temporária fora da procedure, será gerado um erro informando que ela não existe, pois o escopo dela é apenas dentro da procedure que já foi removida da CALL STACK.

Msg 208, Level 16, State 0, Line 119

Invalid object name ‘#TABELA_TESTE_TEMPORARIA_PROCEDURE’.

  • Exemplo 3 – CALL STACK:

Por fim, tentei resumir a execução com um trecho de código e com uma representação da CALL STACK.

Repare que a tabela temporária “#TABELA_TESTE_TEMPORARIA” é criada no início do Bloco 1 e fica disponível na CALL STACK até o final da sessão.

No Bloco 2, a procedure “stpTeste_1_Escopo_Temp_Local” é inserida na CALL STACK, executa e depois é removida.

No Bloco 3, a outra procedure “stpTeste_2_Escopo_Temp_Local” repete o mesmo procedimento na CALL STACK, sendo incluída, executada e depois removida (inclusive a tabela temporária que foi criada dentro dela).

Por fim, o Bloco 1 será removido da CALL STACK quando a sessão for finalizada ou quando a tabela temporária for dropada explicitamente.


Função “OBJECT_ID” x tempdb

Eu gosto bastante de utilizar o “IF” abaixo com a função “OBJECT_ID” para validar se uma tabela já existe, se ela já existir eu faço um DROP TABLE e depois o CREATE TABLE.

Isso evita o erro abaixo, caso a tabela já esteja criada.

Msg 2714, Level 16, State 6, Line 10

There is already an object named ‘NomeTabela’ in the database.

Vou mostrar uma situação quando utilizamos as tabelas temporárias e a função “OBJECT_ID”.

Repare que a função só encontra a tabela temporária “#TABELA_TESTE_TEMPORARIA’” quando executamos na database “tempdb”. Ao executar conectado na database ‘Traces’ o resultado é NULL.

Para contornar esse problema quando você utilizar uma tabela temporária, basta utilizar a função da seguinte forma:

OBJECT_ID(‘tempdb..NomeTabela’)

OBS: Recomendo que utilize sempre o ‘tempdb’ minúsculo, pois se você colocar alguma letra maiúscula (por exemplo, Tempdb) o resultado pode ser FALSO, caso a database ‘tempdb’ esteja utilizando alguma COLLATION com CASE SENSITIVE (ou seja, diferencia letras maiúsculas e minúsculas).

Portanto, o que eu recomendaria nesse caso seria o script abaixo.

OBS: No DROP TABLE você NÃO precisa especificar o ‘tempdb’ antes do nome da tabela.


Referências:

Segue abaixo mais alguns links como referência:

https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

https://www.red-gate.com/simple-talk/sql/t-sql-programming/temporary-tables-in-sql-server/


Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

2 comentários em “Dicas T-SQL – Tabelas Temporárias – Parte 1 – Tabelas Locais

  1. Pingback: Dicas T-SQL – Tabelas Temporárias – Parte 2 – Tabelas Globais – Luiz Lima

  2. Pingback: Dicas T-SQL – Tabelas Temporárias – Parte 3 – Tabelas Variáveis – Luiz Lima

Deixe uma resposta