Temporary Tables vs. Table Variables


 

O SQL Server oferece recursos próprios para se trabalhar temporariamente com informações, de forma que não é necessário utilizar tabelas físicas para este fim. Assim sendo, as tabelas temporárias e as variáveis de tabela, com suas características e particularidades, costumam ser os meios mais eficientes. Contudo, não há uma regra pré-estabelecida que permita saber de antemão o melhor recurso a ser utilizado. Isto dependerá de cada cenário e são as devidas simulações de bases de produção que dirão qual opção deverá ser escolhida.

O SQL Server oferece suporte a alguns tipos de tabelas temporárias que podem ser muito úteis.

  • Tabela Temporária Local
  • Tabela Temporária Global

As tabelas temporárias locais são criadas usando o símbolo “hashtag” seguido pelo nome da tabela. Por exemplo: #Table_Name. Tabelas temporárias são criadas no banco de dados tempdb. Uma tabela temporária local só é visível para a sessão atual. Não pode ser visto ou usado por consultas fora da sessão em que está declarado, quando a conexão é encerrada, a tabela é automaticamente eliminada.

Tabelas temporárias são criadas e possuem uma representação física no tempdb, apesar de que quando são pequenas e o SQL Server tem memória sobrando, suas páginas permanecem na memória. Quando a memória livre é pouca, o SQL Server persiste a tabela em disco.

O conceito de tabelas temporárias é simples, pelo nome já sabemos, são tabelas utilizadas para armazenamento temporário de dados. Isso quer dizer que funcionam como uma tabela normal, porém, são “dropadas”automaticamente após o encerramento da conexão. Se funcionam como tabelas normais, elas ocupam espaço em disco, se ocupam espaço em disco significam que estão no HD, se estão no HD significam que o desempenho cai, já que o processador precisa buscar os dados e “jogar” na memória RAM e todo aquele conceito de paginação de sistemas operacionais.

A tabela temporária pode ser criada usando INTO e também pode criar ela como criamos uma tabela física.

Criando tabela usando INTO, a partir de uma consulta.

Criando tabela temporária local

Script:

CREATE TABLE #TB_CARROS
(Codigo INT,
Descricao VARCHAR(20))
GO
INSERT INTO #TB_CARROS VALUES(1,’Fusion’)
INSERT INTO #TB_CARROS VALUES(2,’HB 20′)
INSERT INTO #TB_CARROS VALUES(3,’Prisma SPE’)
INSERT INTO #TB_CARROS VALUES(4,’Crossfox’)
INSERT INTO #TB_CARROS VALUES(5,’Elantra’)
GO
SELECT * FROM #TB_CARROS

 

Após criar a tabela, se você tentar consultar em uma outra sessão, irá observar que irá retornar um erro informando que o nome do objeto é inválido. Isso demonstra que a tabela temporária local criada em uma sessão esta fora do escopo em outra sessão.

 

Criando a mesma tabela, só que agora do tipo global

 

Agora podemos realizar a consulta em outra sessão que irá retornar.

 

Resumindo:

As tabelas temporárias têm as seguintes características:

  • Não são armazenadas no database de produção.
  • Ao invés disso, elas são armazenadas no database de sistemas tempdb.
  • Seus nomes são iniciados com # (local) e ##(global).
  • São visíveis apenas ao usuário da conexão (local).
  • São eliminadas quando a conexão é encerrada

Tanto a tabela local, quanto a global só existirão enquanto a sessão que os criou estiver aberta.

 

Table Variables

Diferente das tabelas temporarias as variáveis de tabelas são apenas acessíveis dentro de um único, ou seja, apenas dentro da mesma sessão.

Visando o desempenho e a melhor utilização dos recursos dos servidores de banco de dados, podemos trocar a utilização das tabelas temporárias e passar a utilizar tabelas variáveis.

Porém existem suas vantagens e desvantagens que coloco abaixo para todos analisarem;

 

Vantagens

  • Devido à sua utilização estritamente local, tabelas criadas a partir de variável tipo TABLE não consomem recursos para controle de bloqueios;
  • A manipulação de dados em variáveis tipos TABLE é mais eficiente porque essas operações são minimamente locadas (um ROLLBACK após um INSERT não tem efeito em variáveis tipo TABLE);
  • Em função do seu escopo local, procedures que se utiliza de variáveis tipos TABLE estão sujeitas a um número menor de recopilações quando comparadas às tabelas temporárias.

Desvantagens

  • A vida útil de uma tabela criada a partir de um variável tipo TABLE está limitada ao batch e/ou procedure onde é utilizada;
  • Com variáveis tipos TABLE não é permitido: Alteração da estrutura da tabela; Criação de índices não-cluster; Criação de constraints CHECK, DEFAULT; Criação e/ou atualização de estatísticas.

O diferencial comparado com tabelas temporárias é o log reduzido, o número baixo de recompilações e o ganho de desempenho com a ausência do controle de bloqueios.

Criando uma table variable simples

 

Script:

DECLARE @tbCarros TABLE
( ID INT IDENTITY NOT NULL ,
Descricao VARCHAR(20) );

INSERT INTO @tbCarros VALUES (‘Fusion’)
INSERT INTO @tbCarros VALUES (‘HB 20’)
INSERT INTO @tbCarros VALUES (‘Prima SPE’)
INSERT INTO @tbCarros VALUES (‘Crossfox’)
INSERT INTO @tbCarros VALUES (‘Elantra’)

SELECT * FROM @tbCarros;

Mas apesar das desvantagens das variáveis tipo TABLE é uma ótima opção para armazenamento transitório de dados, em substituição às tabelas temporárias.

 

Abraços.

Share on FacebookTweet about this on TwitterShare on LinkedIn


Escreva um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *