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

Olá @pessoas,

Hoje vamos encerrar a série de posts sobre Tabelas Temporárias, dessa vez falando sobre Tabelas Variáveis e por fim fazendo um resumo dos três tipos de Tabelas Temporárias. #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

Se você não viu os outros posts da série, recomendo fortemente que veja antes de continuar a leitura desse post OK. #ficaadica


Tabelas Variáveis – Conceitos:

Tabelas Variáveis (também conhecidas como “variável de tabela” ou “variável do tipo tabela”) são parecidas com as Tabelas Temporárias Locais, mas são um pouco mais limitadas.

Segue abaixo algumas características das Tabelas Variáveis:

  • Você declara uma Tabela Variável com o comando DECLARE, assim como você declara outras variáveis.
  • Para você utilizar a Tabela Variável ao longo do código (assim como uma variável comum), é necessário que você SEMPRE execute o DECLARE junto, caso contrário o SQL Server não irá reconhecer a Tabela Variável e irá gerar um erro.
  • Assim como as Tabelas Temporárias Locais, as Tabelas Variáveis ficam fisicamente alocadas no Tempdb como uma tabela. Um erro muito comum é pensar que elas ficam alocadas APENAS na memória, mas isso não é verdade!!!
  • Outra semelhança com as Tabelas Temporárias Locais é que as Tabelas Variáveis são visíveis apenas na sessão onde são criadas. Contudo, como elas são variáveis elas possuem um escopo ainda mais limitado apenas no BATCH atual.
  • As Tabelas Variáveis não são visíveis para os BATCHES subsequentes na mesma sessão.
  • As Tabelas Variáveis serão destruídas assim que o BATCH terminar de ser executado.

“Pô Luiz, fiquei um pouco confuso com esses conceitos, me ajuda aí cara!!!”

Calma que agora vamos ver esses conceitos na prática com alguns exemplos. #gogogo


Tabelas Variáveis – Declaração e Escopo:

Segue um exemplo de como declarar uma Tabela Variável:

Como podemos ver, o que fica um pouco diferente dos outros tipos de tabelas é apenas a declaração. A manipulação da tabela (SELECT, INSERT, UPDATE, DELETE, JOIN, ETC.) será idêntica as demais.

Agora vamos executar o mesmo script anterior, mas vamos incluir um GO e novamente um SELECT no final e comparar o resultado.

Ele vai exibir o mesmo resultado anterior, mas dessa vez vai terminar a execução com o erro abaixo:

Msg 1087, Level 15, State 2, Line 29

Must declare the table variable “@Tabela_Variavel”.

Isso aconteceu porque o comando GO é utilizado para separar os BATCHES. Você pode ter N BATCHES em uma mesma sessão.

O ESCOPO de uma Variável ou Tabela Variável é apenas dentro do BATCH onde ela foi criada.

Por exemplo, se você criar uma variável dentro do BATCH 1, ela não será visível para os demais BATCHES. Foi exatamente isso que gerou o erro no exemplo anterior.

BACTH 1

GO

BATCH 2

GO

.

.

BATCH N


Tabelas Variáveis – Transações (COMMIT e ROLLBACK):

O Brent Ozar fez um post MUITO LEGAL sobre a utilização de variáveis e transações.

O que me chamou a atenção nesses dois exemplos que ele utilizou é a quantidade de pessoas que ficaram na dúvida e não conhecem esse comportamento do SQL Server. Vou comentar também o perigo e os problemas que isso pode causar.

OBS: Em cada uma das questões abaixo, escolha uma opção antes de executar o script e ver a resposta correta OK.

Exemplo 1 – Variáveis:

“Qual será o valor final da variável @MySalary? 100000 ou 200000?”

Exemplo 2 – Tabelas Variáveis:

“Quantas linhas serão retornadas na tabela @People? 3, 1 ou nenhuma?”

Exemplo 3 (Bônus – Luiz Vitor) – Tabelas Temporárias Locais:

“Quantas linhas serão retornadas na tabela #People? 3, 1 ou nenhuma?”

Respostas:

Referência: https://www.brentozar.com/archive/2020/08/half-of-you-dont-understand-variables-and-transactions/

A “Pegadinha do Malandro“ nesse caso é que Variáveis ou Tabelas Variáveis NÃO são afetadas pelas transações, ou seja, o ROLLBACK não irá desfazer as alterações!

  • Exemplo 1 – O resultado final será 200000, pois a alteração no valor da Variável @MySalary não será desfeita com o ROLLBACK.
  • Exemplo 2 – Irá retornar 3 linhas, pois novamente o ROLLBACK não teve nenhum efeito sobre os INSERTs que foram feitos na Tabela Variável @People.
  • Exemplo 3 – Diferente dos dois exemplos anteriores, agora estamos utilizando uma Tabela Temporária LOCAL e dessa vez o ROLLBACK irá funcionar e desfazer os INSERTs que foram feitos na tabela #People. Portanto, o resultado é que nenhuma linha será retornada.

IMPORTANTE: Repare que o comportamento de uma Tabela Variável (Exemplo 2) é diferente de uma Tabela Temporária Local (Exemplo 3). Portanto, tome muito CUIDADO quando for escolher o tipo de tabela que irá utilizar, pois você pode ter alguns resultados inesperados se fizer a escolha errada!!!

Se você não acertou as respostas, fique tranquilo, pois muita gente confunde e erra isso mesmo. Contudo, espero que após ler esse post você consiga entender a diferença e não cometa esse tipo de erro.

Segue abaixo também um link da Documentação Oficial da Microsoft sobre Variáveis:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-ver15


Tabelas Variáveis – Performance:

Não vou fazer Testes de Performance nesse post para comparar cada um dos tipos das Tabelas Temporárias. O que eu gostaria de ressaltar aqui é o seguinte:

  • Tabelas Variáveis – Possuem algumas limitações de performance!!!

“Tá Luiz, mas o que isso muda na minha vida???”

Acontece que se você utilizar uma Tabela Variável com uma grande quantidade de dados, você pode ter alguns problemas de performance (lentidão).

No post abaixo do Gustavo Moura Fé, ele mostra um exemplo que fica bem nítido a diferença de performance ao utilizar uma Tabela Variável x Tabela Temporária Local.

https://comunidadesqlserver.wordpress.com/2014/07/21/tabelas-temporarias-x-variaveis-de-tabela/

Segue alguns trechos do post:

“Vemos, então, que é necessário avaliar bem nossos requisitos e a massa de dados com a qual iremos trabalhar. Com isso trago o Tuning realizado numa SP que utilizava uma variável de tabela e, quando o número de linhas aumentava muito, seu tempo de execução subia drasticamente.

“Aqui fica clara a diferença no comportamento da SP em relação ao número de linhas. Enquanto na versão anterior (utilizando variável de tabela) a SP tinha uma duração de aproximadamente 2 horas e 15 minutos para 75 mil linhas, após a alteração (utilizando tabela temporária) a SP possui um tempo de execução de aproximadamente 5 minutos para mais de 100 mil linhas!”

  • Observação Importante:

A partir do SQL Server 2019, tivemos alguns ganhos de performance na utilização das Tabelas Variáveis“Table variable deferred compilation”.

Referência: https://docs.microsoft.com/pt-br/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15#table-variable-deferred-compilation


Resumo – Tabelas Temporárias: Qual tipo devo utilizar?

A resposta para essa pergunta é: DEPENDE!!!

Agora que você já conhece um pouco mais sobre as Tabelas Temporárias, você deve ser capaz de identificar os possíveis ganhos e problemas de cada um desses tipos.

Deixo aqui mais um link como referência com algumas comparações entre as Tabelas Temporárias.

https://www.mssqltips.com/sqlservertip/1556/differences-between-sql-server-temporary-tables-and-table-variables/

Para finalizar essa série de posts, vou fazer um pequeno resumo com os três tipos de Tabelas Temporárias:

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

  • ESCOPO: 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.
  • UTILIZAÇÃO: Recomendo que você utilize quando precisar criar uma tabela de forma temporária e que ela seja única para a sessão atual e não seja afetada por outras transações externas.

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

  • ESCOPO: Visíveis para qualquer sessão, ou seja, qualquer sessão pode acessar ou alterar uma Tabela Temporária Global.
  • UTILIZAÇÃO: Recomendo que você utilize quando precisar compartilhar os dados de uma tabela de forma temporária entre várias sessões. Nesse caso, você deve tomar cuidado com as alterações nessa tabela para não afetar o resultado final de forma inesperada (pode ser alterada por uma transação externa).

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

  • ESCOPO: Apenas dentro do BATCH que irá executar. Ela não é compartilhada entre BATCHES na mesma sessão. Também não é compartilhada com outras sessões externas.
  • UTILIZAÇÃO: Recomendo que utilize apenas para uma pequena quantidade de dados (até 100 linhas), pois as Tabelas Variáveis possuem algumas limitações de performance. Também tome cuidado com a questão do COMMIT / ROLLBACK que não irão afetar as alterações nas Tabelas Variáveis.

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

6 comentários em “Dicas T-SQL – Tabelas Temporárias – Parte 3 – Tabelas Variáveis

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

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

    • Luiz Lima Autor do postResponder

      Fala Gustavo, obrigado mano! Fico feliz em ter ajudado! =)

      Vou tentar fazer mais algumas séries assim também, Netflix que se cuide kkkk #gogogo

      Abraço,
      Luiz Vitor

Deixe uma resposta