MENU
  1. Início
  2. SQL
  3. Tabela temporária vs Variável de tabela no SQL

Tabela temporária vs Variável de tabela no SQL

SQL

Entender quando usar uma tabela temporária ou uma variável de tabela no SQL é essencial ao armazenar dados temporariamente. Ambas oferecem funcionalidades semelhantes, mas possuem características e cenários de aplicação diferentes. Este artigo explora as particularidades de cada uma e oferece orientações sobre como fazer a escolha ideal.

Índice

Características da Tabela Temporária

Uma tabela temporária é usada para armazenar dados temporários no SQL, e normalmente utiliza o prefixo # ou ##.

Como Criar

Uma tabela temporária é criada usando o comando CREATE TABLE. Por exemplo:

CREATE TABLE #TempTable (
    ID INT,
    Name NVARCHAR(50)
);

Uso

Tabelas temporárias são ideais para armazenar grandes quantidades de dados temporariamente e reutilizá-los em múltiplas instruções.

Desempenho

Tabelas temporárias salvam os dados no disco, proporcionando desempenho estável com grandes volumes de dados, mas são vulneráveis a I/O de disco.

Escopo

O escopo de uma tabela temporária está limitado à sessão ou ao batch. Ela é automaticamente excluída quando a sessão é encerrada.

-- Exemplo de uso em uma sessão
INSERT INTO #TempTable (ID, Name) VALUES (1, 'Alice');
SELECT * FROM #TempTable;

Características da Variável de Tabela

A variável de tabela é tratada como uma variável no SQL e é usada principalmente para operações temporárias de dados.

Como Declarar

Uma variável de tabela é declarada usando o comando DECLARE. Por exemplo:

DECLARE @TableVar TABLE (
    ID INT,
    Name NVARCHAR(50)
);

Uso

Variáveis de tabela são adequadas para manipular pequenas quantidades de dados ou armazená-los temporariamente dentro de um procedimento armazenado.

Desempenho

Como os dados são armazenados na memória, variáveis de tabela oferecem desempenho rápido para pequenas operações de dados. Porém, não são adequadas para grandes volumes.

Escopo

O escopo de uma variável de tabela está limitado ao batch ou procedimento armazenado onde foi declarada. Ela é automaticamente liberada quando sai do escopo.

-- Exemplo de uso em um batch
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice');
SELECT * FROM @TableVar;

Comparação de Desempenho

O desempenho de tabelas temporárias e variáveis de tabela varia dependendo da quantidade de dados e do tipo de operação. A seguir, compararemos o desempenho de cada uma.

Com Pequenas Quantidades de Dados

Para operações com pequenas quantidades de dados, as variáveis de tabela são mais eficientes, pois a operação ocorre na memória e não há overhead de I/O de disco.

-- Exemplo de bom desempenho com variável de tabela
DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM @TableVar;

Com Grandes Quantidades de Dados

Para grandes quantidades de dados, as tabelas temporárias são mais adequadas, pois os dados são armazenados no disco e, portanto, não são tão limitados pela memória. Além disso, é possível criar índices.

-- Exemplo de uso de tabela temporária com grande quantidade de dados
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name)
SELECT ID, Name FROM LargeSourceTable;
CREATE INDEX idx_temp ON #TempTable(ID);
SELECT * FROM #TempTable;

Com ou Sem Índices

Tabelas temporárias permitem a criação de índices para melhorar o desempenho. Já as variáveis de tabela têm restrições quanto à criação de índices, o que pode prejudicar o desempenho em consultas complexas.

Consultas Complexas

Tabelas temporárias são mais eficientes ao lidar com consultas complexas ou operações de junção. As variáveis de tabela não armazenam o plano de consulta em cache, o que pode levar a um desempenho inferior em consultas mais elaboradas.

-- Exemplo de uso de tabela temporária em consultas complexas
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name)
SELECT ID, Name FROM LargeSourceTable;
SELECT t1.ID, t2.Name
FROM #TempTable t1
JOIN AnotherTable t2 ON t1.ID = t2.ID;

Diferenças de Escopo e Tempo de Vida

Tabelas temporárias e variáveis de tabela possuem diferentes escopos e tempos de vida. Compreender essas diferenças é importante para a escolha adequada.

Escopo da Tabela Temporária

Uma tabela temporária é válida dentro da sessão ou batch onde foi criada. Ela é automaticamente excluída ao término da sessão. Tabelas temporárias locais (#TempTable) são acessíveis apenas na sessão atual, enquanto tabelas temporárias globais (##TempTable) podem ser acessadas por outras sessões.

-- Exemplo de escopo de tabela temporária
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
-- A tabela #TempTable é excluída automaticamente quando a sessão é encerrada

Escopo da Variável de Tabela

Uma variável de tabela é válida apenas dentro do batch ou procedimento armazenado onde foi declarada. Ao sair do escopo, a variável é automaticamente liberada.

-- Exemplo de escopo de variável de tabela
DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
-- A variável @TableVar é liberada automaticamente ao final do batch

Diferenças de Tempo de Vida

O tempo de vida de uma tabela temporária depende da sessão. Ela permanece ativa enquanto a sessão estiver aberta. Em contraste, uma variável de tabela é liberada assim que o batch ou procedimento armazenado termina.

Exemplos de Aplicação de Escopo e Tempo de Vida

Se for necessário compartilhar dados entre múltiplos batches ou procedimentos armazenados em uma sessão, a tabela temporária é a escolha ideal. Já a variável de tabela é útil para armazenar dados temporários dentro de um único batch ou procedimento.

-- Exemplo de aplicação de tabela temporária
CREATE TABLE #SessionTemp (ID INT, Name NVARCHAR(50));
INSERT INTO #SessionTemp (ID, Name) VALUES (1, 'Alice');

-- Dados acessíveis em outro batch ou procedimento armazenado
SELECT * FROM #SessionTemp;

-- Exemplo de aplicação de variável de tabela
DECLARE @BatchVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @BatchVar (ID, Name) VALUES (1, 'Alice');
SELECT * FROM @BatchVar;
-- A variável é liberada ao final do batch

Cenários de Aplicação para Tabelas Temporárias e Variáveis de Tabela

Ambas, tabelas temporárias e variáveis de tabela, são adequadas para diferentes cenários de uso. A seguir, descreveremos os cenários ideais para cada uma.

Cenários Ideais para Tabelas Temporárias

Tabelas temporárias são especialmente eficazes nos seguintes cenários:

Manipulação de Grandes Volumes de Dados

Quando há a necessidade de armazenar e manipular grandes volumes de dados temporariamente em múltiplas instruções, tabelas temporárias são ideais, pois salvam os dados no disco.

CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) SELECT ID, Name FROM LargeTable;
SELECT * FROM #TempTable WHERE ID > 1000;

Criação de Índices

Tabelas temporárias são ideais quando há necessidade de criar índices para melhorar o desempenho das consultas.

CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) SELECT ID, Name FROM LargeTable;
CREATE INDEX idx_temp ON #TempTable(ID);
SELECT * FROM #TempTable WHERE ID > 1000;

Compartilhamento de Dados entre Sessões

Quando há a necessidade de compartilhar dados entre múltiplos batches ou procedimentos em uma mesma sessão, as tabelas temporárias são a escolha adequada.

CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable (ID, Name) VALUES (1, 'Alice');
-- Dados acessíveis de outro batch ou procedimento
SELECT * FROM #TempTable;

Cenários Ideais para Variáveis de Tabela

Variáveis de tabela são eficazes nos seguintes cenários:

Manipulação de Pequenos Volumes de Dados

Para pequenas operações de dados, as variáveis de tabela são ideais, pois as operações ocorrem na memória, proporcionando alta velocidade.

DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM @TableVar;

Armazenamento Temporário de Dados

Variáveis de tabela são úteis para armazenar dados temporariamente dentro de um único batch ou procedimento armazenado.

DECLARE @TableVar TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TableVar (ID, Name) VALUES (1, 'Alice');
-- A variável é liberada ao final do batch
SELECT * FROM @TableVar;

Uso dentro de Triggers

Variáveis de tabela são ideais para armazenar dados temporários dentro de triggers, onde o escopo é limitado e o desempenho rápido é uma vantagem.

CREATE TRIGGER trgAfterInsert ON SampleTable
AFTER INSERT AS
BEGIN
    DECLARE @InsertedData TABLE (ID INT, Name NVARCHAR(50));
    INSERT INTO @InsertedData (ID, Name)
    SELECT ID, Name FROM Inserted;
    -- Operações com os dados dentro do trigger
    SELECT * FROM @InsertedData;
END;

Boas Práticas em SQL

A seguir, apresentamos algumas boas práticas para garantir o uso adequado de tabelas temporárias e variáveis de tabela, maximizando o desempenho e a eficiência das consultas SQL.

Escolha com base no volume de dados

Utilize variáveis de tabela para manipulações de pequenos volumes de dados e tabelas temporárias para grandes volumes, otimizando o desempenho.

-- Pequenos volumes de dados com variáveis de tabela
DECLARE @SmallData TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @SmallData (ID, Name) VALUES (1, 'Alice'), (2, 'Bob');
SELECT * FROM @SmallData;

-- Grandes volumes de dados com tabelas temporárias
CREATE TABLE #LargeData (ID INT, Name NVARCHAR(50));
INSERT INTO #LargeData (ID, Name) SELECT ID, Name FROM LargeSourceTable;
SELECT * FROM #LargeData;

Uso de Índices

A criação de índices em tabelas temporárias pode melhorar significativamente o desempenho em consultas com grandes volumes de dados.

CREATE TABLE #IndexedTable (ID INT, Name NVARCHAR(50));
INSERT INTO #IndexedTable (ID, Name) SELECT ID, Name FROM LargeSourceTable;
CREATE INDEX idx_temp ON #IndexedTable(ID);
SELECT * FROM #IndexedTable WHERE ID > 1000;

Projeção de escopo consciente

Projeções de escopo e tempo de vida ajudam a evitar o consumo desnecessário de recursos.

-- Dados usados durante toda a sessão, armazenados em tabela temporária
CREATE TABLE #SessionData (ID INT, Name NVARCHAR(50));
INSERT INTO #SessionData (ID, Name) VALUES (1, 'Alice');
-- Acesso a dados fora do escopo do batch
SELECT * FROM #SessionData;

-- Dados usados apenas em um batch, armazenados em variável de tabela
DECLARE @BatchData TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @BatchData (ID, Name) VALUES (1, 'Alice');
-- Dados liberados ao final do batch
SELECT * FROM @BatchData;

Facilidade de Manutenção

As variáveis de tabela são liberadas automaticamente ao final do batch, prevenindo vazamentos de memória e facilitando a manutenção. São especialmente recomendadas para uso dentro de procedimentos armazenados.

-- Exemplo de uso de variável de tabela em procedimento armazenado
CREATE PROCEDURE SampleProcedure
AS
BEGIN
    DECLARE @ProcData TABLE (ID INT, Name NVARCHAR(50));
    INSERT INTO @ProcData (ID, Name) VALUES (1, 'Alice');
    SELECT * FROM @ProcData;
END;

Teste e Monitoramento de Desempenho

É essencial realizar testes em cenários específicos e monitorar o desempenho real para fazer a escolha mais adequada.

-- Realize testes em cenários reais
-- Use ferramentas de monitoramento de desempenho para garantir a eficiência

Conclusão

Resumindo, tabelas temporárias são adequadas para operações de grandes volumes de dados e criação de índices, além de serem úteis quando há necessidade de compartilhar dados entre sessões. Variáveis de tabela, por outro lado, são ideais para operações com pequenos volumes de dados e armazenamento temporário dentro de batches ou procedimentos. Escolher a melhor opção para cada cenário maximizará o desempenho de suas consultas SQL.

SQL
Armazenamento de Dados Desempenho Escopo Grandes Dados Indexação Pequenos Dados SQL Tabela Temporária Variável de Tabela
Índice