Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

Archive for agosto \16\UTC 2008

Variáveis tipo TABLE

Posted by karuta em agosto 16, 2008

Definição

Variável tipo TABLE nada mais é do que um tipo especial de variável que pode ser utilizada para armazenamento temporário de dados, de maneira similar a tabelas temporárias.

Vantagens

·         Devido à sua utilização estritamente local, tabelas criadas a partir de variáveis tipo TABLE não consomem recursos para controle de bloqueios;
·         A manipulação de dados em variáveis tipo TABLE é mais eficiente porque essas operações são minimamente logadas (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 utilizam de variáveis tipo TABLE estão sujeitas a um número menor de recompilações quando comparadas às tabelas temporárias.

Desvantagens

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

Utilização

Variáveis tipo TABLE são uma ótima opção para armazenamento transitório de dados, em substituição às tabelas temporárias. Exemplos de utilização:

·         Para criar uma variável tipo TABLE

declare @tab TABLE (cod_cliente int, qtde_pedidos int)

·         Manipulando dados numa variável tipo tabela

declare @tab TABLE (cliente varchar(10), qtde_pedidos int)
insert into @tab
   select customerId, qtde_pedidos=count(*)
   from orders
   where customerId like (‘v%’)
   group by customerId
select * from @tab
update @tab set qtde_pedidos = qtde_pedidos + 1
delete from @tab
———————————————————————
(3 row(s) affected)
cliente    qtde_pedidos
———- ————
VAFFE      11
VICTE      10

VINET      5
(3 row(s) affected)
(3 row(s) affected)
(3 row(s) affected)

·         Executando Join com tabela criada à partir de uma variável tipo TABLE

declare @tab TABLE (cliente varchar(10), qtde_pedidos int)
insert into @tab
   select customerId, qtde_pedidos=count(*)
   from orders
   where customerId like (‘v%’)
   group by customerId
select *
from @tab t
inner join
    customers c
on t.cliente = c.customerId
———————————————————————(3 row(s) affected)
cliente    razao_social                            
———- —————————————-
VAFFE      Vaffeljernet
VICTE      Victuailles en stock
VINET      Vins et alcools Chevalier
(3 row(s) affected)

Considerações Finais

Diferentemente do que muitos pensam, 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 performance com a ausência do controle de bloqueios.

Conclusão

Variáveis tipo TABLE são uma versão “light” das equivalentes tabelas temporárias e devem ser utilizadas no dia-a-dia para armazenamento temporário de pequenos volumes de dados.

Artigo escrito por: Paulo Ribeiro (psribeiro@hotmail.com) é Microsoft MCDBA e membro da equipe editorial da SQL Magazine.

Anúncios

Posted in sql server | Leave a Comment »

operadores EXCEPT e INTERSECT – sql2005

Posted by karuta em agosto 4, 2008

O EXCEPT e o INTERSECT são operadores novos no SQL SERVER. Eles nos permitem encontrar registros que são comuns em dois sets de dados (tablelas, views, etc) ou registros em um set que não esteja no outro.

EXCEPT

O Operador EXCEPT pode ser usado como alternativas ao operador AND e ao comando NOT EXISTS.

Exemplo 1

Vamos retornar os produtos que tem um preço unitário superior a 18 com o estoque menor que 20 unidades.

Com AND NOT

Select
* from products
where unitprice > 18 AND NOT unitsinStock <20

Com EXCEPT

Select
* from products where unitprice > 18
EXCEPT
Select * from products
where unitsinStock < 20

Exemplo 2

Agora, vamos listar o id dos produtos que não tem pedidos feitos.

Com NOT EXISTS

select
prod.productid from products as prod
where not exists
(
select ord.productid from [order Details] as ORD
         where ord.productid = prod.productid )

Com EXCEPT

select
prod.productid from products as prod
EXCEPT
select
ord.productid from [order Details] as ORD

INTERSECT

O operador INTERSECT é muito similar ao INNER JOIN.

Suponha-se que temos a tabela Orders e uma outra tabela similar chamada ImportedOrders, e queremos saber quais os pedidos da tabela ImportedOrders estão duplicados na tabela Orders.

Com INNER JOIN

Select
ord.orderid, ord.customerid from orders as ord
inner join importedOrders as iord on ord.orderid = iord.orderid and ord.customerid = iord.customerid

Com INTERSECT

select
orderid, customerid  from orders
intersect
select
orderid, customerid  from importedorders

Posted in sql server | Leave a Comment »