Tenho notado que este recurso é pouco utilizado por desenvolvedores T-SQL. Analisando as consultas realizadas nas bases de dados em que administro. Observei que muitos trechos de código T-SQL poderiam ser substituídos por tabelas variáveis para ganho de desempenho.
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 é se passasse 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; Uma variável tipo TABLE não pode ser o destino de INSERT EXEC ou SELECT INTO; Uma variável tipo TABLE só pode ser referenciada por um comando SP_EXECUTESQL se a variável for criada.
Variáveis tipo TABLE também consomem recursos do TempDB – na verdade tanto tabelas temporárias quanto variáveis tipo TABLE serão criadas em memória para pequeno volume de dados. O diferencial das 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.
Mas apesar das desvantagens variáveis tipo TABLE é uma ótima opção para armazenamento transitório de dados, em substituição às tabelas temporárias.
Exemplos de utilização:
-- Declaração Tabela variáveis em memória
DECLARE @Tabela TABLE
(
codigo smallint not null,
nome varchar(20)
)
INSERT INTO @Tabela(codigo, nome) VALUES(1, 'leivio')
INSERT INTO @Tabela(codigo, nome) VALUES(2,'Nome1')
INSERT INTO @Tabela(codigo, nome) VALUES(3,'Nome1')
/*Exemplo de Select*/
SELECT * FROM @Tabela
/*Exemplo de Update*/
UPDATE @Tabela SET Nome = SUBSTRING(Nome, 1 , LEN(Nome) - 4)
/*Exemplo de Delete*/
DELETE FROM @Tabela WHERE codigo = 2
Não podemos utilizar Index NO-CLUSTERED em tabelas variavies. Porém podemos obter um resultado parecido com PRIMARY KEY ou UNIQUE. “Pois o SQL Server garante as CONTRAINTS utilizando Índex”.
Ex:
-- Declaração Tabela variaveis com UNIQUE
DECLARE @Tabela TABLE
(
codigo smallint UNIQUE,
nome varchar(20) NOT NULL DEFAULT('Desconhecido')
)
-- Declaração Tabela variavies com Chave primaria com incrementos
DECLARE @Tabela TABLE
(
codigo smallint IDENTITY(1,1) PRIMARY KEY,
nome varchar(20) NOT NULL
)
Não podemos utilizar as tabelas variáveis com SQL Dinâmico.
Ex:
DECLARE @Tabela TABLE
(
codigo smallint IDENTITY(1,1) PRIMARY KEY,
nome varchar(20) NOT NULL DEFAULT('Desconhecido')
)
EXEC sp_executesql N'SELECT * FROM @Tabela'
O Seguinte Erro é mostrado
Mensagem 1087, Nível 15, Estado 2, Linha 1
Must declare the table variable "@Tabela".
“Lembrando para não utilizar despercebidamente o “batch separator” que por padrão no SQL Server é o ‘‘GO”, pois ele informa que o comando(batch) terminou e, portanto limpa todas as variáveis.
Ex:
DECLARE @Tabela TABLE
(
codigo smallint not null,
nome varchar(20)
) -- Declaração Tabela temporária em memória
SELECT * FROM @Tabela
GO
SELECT * FROM @Tabela <-- ERRO - Server: Msg 1087, Level 15, State 2, Line 1 É necessário declarar a variável de tabela "@Tabela".
As tabelas variáveis precisam de um alias para ser utilizadas em consultas JOIN.
-- Exemplo
SELECT CODIGO, PRODUTO, PV.SOMA FROM PRODUTO P INNER JOIN @PRODUTOTOTAL PV ON P.CODIGO = PV.CODIGO
Tabelas variáveis não podem ter a sua estrutura modificada após a sua declaração. E também não se utiliza das estatísticas para “otimização” do plano de execução. E as tabelas variáveis não fazem parte das transações. Ou seja,
-- Exemplo
DECLARE @Tabela TABLE
(
codigo smallint,
nome varchar(20)
)
INSERT INTO @Tabela(codigo, nome) VALUES(1, 'Nome1')
INSERT INTO @Tabela(codigo, nome) VALUES(2, 'Nome2')
BEGIN TRAN
DELETE FROM @Tabela where Codigo = 1
ROLLBACK
SELECT * FROM @Tabela
Resultado:
codigo nome
------ --------------------
2 Nome2
Podemos observar que existe vantagens e desvantagens em se utilizar as tabelas variaveis. Porem se bem utilizadas podemos ter ganhos excelentes de performace.
