As novidades do T-SQL no SQL Server 2005

Extraído do site: www.bufaloinfo.com.br

A nova versão do SQL Server será lançada ainda este ano e estará trazendo muitas novidades na linguagem T-SQL. Irei mostrar algumas destas novidades neste artigo.

Row_Number

Agora nós temos, finalmente, uma função para numeração de linhas no T-SQL, simplificando muito o que antes tinhamos que realizar com alguns truques.

Veja truques que eram utilizados anteriormente para numeração de linhas :

http://www.bufaloinfo.com.br/dicas.asp?cod=568

http://www.bufaloinfo.com.br/dicas.asp?cod=415

http://www.bufaloinfo.com.br/dicas.asp?cod=315

Utilizando a função row_number podemos não só numerar as linhas de uma tabela como também fazer essa numeração em uma ordem específica, diferente da ordem na qual as linhas serão exibidas.

Veja :

select row_number() over (order by customerid), * from customers

A clausula over é obrigatória, determinando a ordem em que a numeração deve ser feita. Porém o resultado do select pode ser exibido em qualquer ordem, veja :

select row_number() over (order by customerid), * from customers
order by country

Analisando o plano de execução destas querys, observamos que o mais pesado foi o processamento de ordenação dos dados, 2 no caso da 2a query


image002.jpg 

Uma questão interessante é como isso irá reagir a um processo de indexação ? O que acontece quando colocarmos índices em customerid e country ?

De inicio devemos lembrar que um índice precisa ser clustered ou cover para ser bem utilizado em um processo de ordenação. Isso porque a ordenação envolve uma quantidade de linhas muito grande, então se para cada linha é necessário realizar um bookmark lookup a query fica muito pesada. Muitas vezes o servidor irá preferir fazer um sort em memória do que usar o índice. As excessões são justamente os índices clustered, que já estão em ordem física e os índices cover, que contém todos os campos necessários e por isso evitam o bookmark lookup

Vejamos então os índices de performance desta query :

Com clustered em country e nonclustered em customerid : 0.0798

image004.jpg 


Com clustered em customerid e nonclustered em country : 0.0477

image006.jpg 

Interessante observar que neste 2o caso o índice nonclustered foi ignorado. Isso porque, como citei, ele precisaria ser cover ou clustered para ser usado em uma ordenação e não é nenhum dos dois.

Pelo query plan observamos que primeiramente o SQL Server faz um sequence project para numerar as linhas de depois faz a ordenação segundo a query.

Vamos alterar um pouco a query, veja :

select row_number() over (order by customerid), customerid,companyname from customers
order by country

Agora podemos criar um indice cover com country e companyname, além do clustered em customerid. Pronto : 0.0252 . Bem melhor. Vale lembrar que os números, neste momento, significam muito pouco. O fato de aumentarem ou diminuirem sim, que quer dizer algo.

Observando o recurso de numeração de linhas, a idéia natural que passa pela mente de todo programador é utilizar este recurso para paginação de dados na web.

Porém a numeração de linhas é sempre processada antes da clausula where da query. Consequentemente não podemos utilizar o número de linha como critério a menos que utilizemos sub-querys.

Veja como fica uma procedure que pode ser utilizada para a paginação :

Create Procedure PaginarCliente
@LinhaInicial int, @TotalLinhas int
as
select * from
(select row_number() over (order by customerid) as RowNum,* from customers) c
where rownum between @linhainicial and @linhainicial + @totallinhas -1
order by customerid

Testando o plano de execução desta procedure, observamos que todas as linhas são numeradas antes que seja feita a devolução das 5 linhas que se deseja, ou seja, o processamento de numeração passa por toda a tabela.

A query original, sem nenhum indice, tem um custo de 0.0478 . Quando aplicamos um indice clustered em customerid esse indice cai para 0.0080 . O plano de execução continua informando que foi feita uma varredura nas 91 linhas da tabela customers, mas não estou tão certo de que isso tenha sido feito.

Podemos também utilizar o novo recurso de síntaxe chamado CTE - Common Table Expression - para montar esta mesma procedure. Para quem já utilizou querys MDX esse recurso não é novidade.

Veja como fica :

Create Procedure PaginarClienteCTE
@LinhaInicial int, @TotalLinhas int
as
With O
as (select row_number() over (order by customerid) as RowNum,* from customers )
select * from O
where rownum between @linhainicial and @linhainicial + @totallinhas -1
order by customerid

É uma forma mais simples e poderosa de criarmos sub-querys e o resultado no plano de execução foi identico.

Vejamos agora 2 outras funções para numeração de registros. A função row_number() faz uma numeração sequencial. Já a função rank(), além de fazer a numeração, analisa o conteúdo do campo que está sendo ordenado. Se houverem valores iguais a numeração e igual. Por exemplo, uma ordenação por país : Todos os registros que forem do mesmo país receberão o mesmo numero.

A função Dense_Rank() é muito semelhante com a função Rank(), com uma diferença bem sutil. A função Rank faz uma numeração sequencia por registro, então entre um país e outro existe um salto numérico. Já a função Dense_Rank faz a numeração por agrupamento (país, no exemplo), evitando este salto numérico. Observe as imagens e compare as duas.

select dense_rank() over (order by country),
rank() over (order by country), country, companyname
from customers

image008.jpg 

Testando a performance, vemos que sem índices a query tem um custo de 0.0477 . Quando criamos um índice clustered em Customerid e um indice nonClustered em country é feito um Nested Loop entre os dois indices e a performance fica em 0.0400 . Aqui temos uma curiosidade muito interessante : O SQL Server 2005 não utiliza mais os bookmark lookups. A documentação indica que não são mais utilizados, pelo que pude observar nos testes eles foram substituidos por nested loops entre indices NonClustered e Clustered.

Mas a melhor opção fica sendo mesmo o índice clustered, com um resultado de 0.0079. Isso considerando que o campo do Rank e da exibição é o mesmo. Se forem diferentes, o melhor resultado fica com o índice clustered no campo do rank (a numeração precisa ser feita primeiro, para depois ser feita uma ordenação). A adição de índices covered também podem melhorar a query.

Recursividade

A possibilidade de criar querys recursivas é um novo e poderoso recurso do T-SQL. Esse recurso é possibilitado pelo uso das CTEs.

O exemplo mais típico da aplicação deste recurso é um auto-relacionamento. A tabela Employees relaciona-se com ela mesma, já que um funcionário possui um chefe e este também é um funcionário. O campo ReportsTo da tabela Employees possui um EmployeeID com o qual o funcionário se relaciona.

Poderíamos tentar o seguinte :

select E.Employeeid,E.LastName,E.ReportsTo,a.Employeeid as Gerente From Employees E,
Employees A where e.reportsto=a.employeeid

Porém com essa query conseguiremos apenas relacionar cada funcionario com seu chefe direto. Precisariamos fazer inúmeros joins para conseguir todos os níveis, fica impraticável, até porque o número de níveis pode variar.

Utilizando uma CTE podemos criar uma query recursiva para obter este resultado. Dentro da CTE podemos criar um UNION com duas querys : Uma query que pegue todos os funcionários com o ReportsTo Null, ou seja, o 1o nível de hierarquia. A segunda query faz um join com a própria CTE, gerando uma recursividade. Irá buscar o 2o nível da hierarquia, mas com a recursividade isso seguirá adiante para o 3o, 4o, e assim por diante.

Veja como fica :

With EmpCTE (EmpId,EmpName,MgrId,Level)
AS
(Select E.EmployeeID, E.LastName, E.ReportsTo,1
From Employees E Where ReportsTo is Null
Union all
select E.EmployeeID, E.LastName, E.ReportsTo,Level +1
From Employees E inner join EmpCte on empcte.empid=E.reportsto
)
select empid,empname, mgrid,level
from empcte

Analisando o plano de execução desta query descobrimos algumas coisas muito interessantes. Aparecem novos elementos no plano de execução chamados Table Spool e Index Spool. Segundo a definição do help significa que os dados estão sendo guardados, de forma oculta, no TempDB, de forma que apenas um table scan seja necessário. Parece uma forma otimizada de uso das antigas tabelas temporárias.

image010.jpg 

Outra questão interessante é o elemento Assert : Segundo a definição dele, faz um teste, podendo abortar uma query. No plano de execução da query acima ele testa uma expressão, comparando com 100. É possível que isso seja o limite máximo de recursividade. MAS NÃO TESTEI, então cuidado com o boato !

image011.gif

Mais compatibilidade com ANSI

O T-SQL ganhou novos operadores para manipulação de dados, operadores existentes no ANSI. Com isso diversas operações antes feitas com subquerys se tornam mais simples e intuitivas. Veja um exemplo :

select * from customers where customerid in (select customerid from orders)

select * from customers where customerid =any (select customerid from orders)

Clausula OUTPUT

As instruções INSERT, DELETE e UPDATE ganharam uma nova clausula, OUTPUT. Em minha opinião a principal utilidade será na instrução insert.

Quando fazemos um insert em uma tabela alguns campos são preenchidos automaticamente, tal como campos identity (auto-numeração) e campos com valores default. Então quando o client precisa obter as informações atualizadas no registro logo após a inserção acaba precisando fazer um SELECT logo após o INSERT.

O DataAdapter já preve isso e automaticamente, ao montar a instrução insert, monta um select para fazer o refresh dos registros no DataSet.

Veja como ficaria uma stored procedure para fazer isso :

create procedure teste1
@t varchar(20) as
set nocount on
insert into teste values (@t)
select * from teste where id=@@identity

Com isso temos duas operações em sequencia : um INSERT e um SELECT. Com o uso da clausula OUTPUT evita-se o 2o SELECT na tabela, otimizando mais este procedimento. Veja como fica :

create procedure teste2
@t varchar(20) as
set nocount on
declare @tab table(id int,testando varchar(20))
insert into teste (testando)
output inserted.* into @tab
values (@t)
select * from @tab

A clausula OUTPUT entra em meio a instrução INSERT, determinando que os dados, logo após serem inseridos, sejam jogados para a variável Table. Após isso basta fazermos um SELECT na variável Table, que conterá apenas o registro inserido.

Testando o plano de execução em uma tabela pequena, a 2a procedure parece mais pesada, pois faz uso de uma variável Table. Porém em uma tabela grande o uso da variável Table será mais leve do que fazer um novo SELECT na tabela.

Mudanças na clausula TOP

A clausula TOP se tornou bem mais versátil. A principal mudança é que agora a clausula TOP passou a aceitar variáveis. Veja um exemplo do que podemos fazer :

create procedure listaProdutos
@topx int
as
select top(@topx) * from products order by unitsinstock

Mas além disso a clausula TOP foi também adicionada em inúmeras outras instruções, tal como UPDATE, DELETE e INSERT. Passei por alguns casos interessantes em que ela teria sido muito útil.

Imagine uma tabela com um milhão de registros e você precisa esvazia-la. O truncate table seria o ideal, mas apenas system administrators podem realizar truncate table. Se você tentar utilizar um delete diretamente provavelmente irá falhar, pois não haverá espaço suficiente no log de transações para guardar os registros deletados. Então como resolver o problema ?

Em versões anteriores teriamos que fazer loops para deletar os registros aos poucos. Mas com a nova clausula TOP basta utiliza-la na instrução DELETE, veja :

Delete top 500 from tabelaMuitoGrande

Analise de dados

A analise de dados da base normalmente deve ser feita pelo Analisys Server, mas a linguagem T-SQL ganhou alguns recursos para isso. Veja a query abaixo :

select productid, productname,quantity*b.unitprice as total,year(c.orderdate) as ano
from products a inner join [order details] b
on a.productid=b.productid
inner join orders c on
b.orderid=c.orderid

Com essa query temos o ano de realização de cada venda de cada produto. Vamos então agrupar :

select a.productid, productname,sum(quantity*b.unitprice) as total,year(c.orderdate) as ano
from products a inner join [order details] b
on a.productid=b.productid
inner join orders c on
b.orderid=c.orderid
group by a.productid,productname,year(c.orderdatE)
order by productid

image013.jpg

E que tal agora se pudessemos inverter a exibição do resultado para simplificar a análise ? Normalmente isso seria muito complicado de fazer, mas não para o novo TSQL, veja :

with O as
(select a.productid, productname,sum(quantity*b.unitprice) as total,year(c.orderdate) as ano
from products a inner join [order details] b
on a.productid=b.productid
inner join orders c on
b.orderid=c.orderid
group by a.productid,productname,year(c.orderdatE))

select * from O
Pivot (sum(total) for ano in ([1996],[1997],[1998])) as pvt

image015.jpg

Aproveitei e abusei um pouquinho do uso das CTEs para demonstrar como ficou simples montar querys com resultados bem complexos.

 

Dennes Torres
MCAD,MCSD,MCSE,MCDBA