Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login

Trabalhando com Tabelas Variáveis

Explicação sobre a utilização de tabelas variaveis em SQL Server.

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login

(opcional) Onde podemos melhorar?

Confirmar voto
0
 (3)  (0)

     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 =

  

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.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Ajude-nos a evoluir: você gostou do post?  (3)  (0)

(opcional) Onde podemos melhorar?

Confirmar voto
Compartilhe:
Ficou com alguma dúvida?