DevMedia
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
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.

[fechar]

Você não gostou da qualidade deste conteúdo?

(opcional) Você poderia comentar o que não lhe agradou?

Confirmo meu voto negativo

     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.



DBA SQL Server com experiência em SQL Server desde da sua versão 6.5. Certificado Microsoft desde 2006 e hoje conta com as seguintes credenciais; MCP | MCTS | MCITP: Database administrator e IBM Certified Database Associate - DB2 [...]

O que você achou deste post?
Conhece a assinatura MVP?
Serviços

Mais posts