Melhorias do T-SQL no SQL Server 2012 (Parte 2)

Posted on março 27, 2012

1


Nessa semana iremos ver o novo recurso chamado paginação de consultas no SQL Server 2012.

Supondo que você desejasse buscar somente os 10 primeiros registros de uma tabela, como você faria? ou os 30 primeiros? ou ainda 500 registros de uma tabela divididas por página?

Iremos aqui utilizar o banco de dados AdventureWorksDW2012http://msftdbprodsamples.codeplex.com/releases/view/55330

a tabela de DimCustomer desse banco de dados.

USEAdventureWorksDW2012

go

SELECT * FROM DimCustomer

image

(Figura 1 – Selecionando Informações da Tabela de DimCustomer)

Desejamos andar entre os registros da tabela , porém para realizar esse requisito podemos utilizar a função ROW_NUMBER(), assim teriamos uma numeração incremental e a partir dela, conseguiriamos andar nos registros. Poderiamos resolver esse problema da seguinte forma:

DECLARE @NumeroPagina INT = 1

DECLARE @NumerosRegistrosPagina INT = 12

 

;WITH DadosClientes AS

(

SELECT ROW_NUMBER() OVER(ORDER BY CustomerKey) AS RowNumberCustomerKey,

        C.CustomerKey,

        C.CustomerAlternateKey,

        C.Title,

        C.FirstName + ‘ ‘ + ISNULL(C.MiddleName,) + ‘ ‘ + LastName AS Name,

        C.BirthDate,

        C.MaritalStatus,

        C.Gender,

        C.EmailAddress

FROM DimCustomer AS C

)

SELECT CustomerKey, CustomerAlternateKey, Title, Name,

        BirthDate, MaritalStatus, Gender, EmailAddress

FROMDadosClientes

WHERE RowNumberCustomerKey BETWEEN(((@NumeroPagina 1) * @NumerosRegistrosPagina) + 1)

     AND(@NumeroPagina * @NumerosRegistrosPagina)

ORDER BY CustomerKey

Aonde o @NumeroPagina é a quantidade de páginas que desejamos dividar a consulta nesse caso queremos a partir da 1ª página e @NumeroRegistrosPaginas é a quantidade de registros mostrados por página.

image

(Figura 2 – Paginação das Informações Utilizando ROW_NUMBER())

Porém nessa consulta é necessário a criação de uma CTE contendo ROW_NUMBER o que pode não ser muito eficiente e nem performático.

Para melhorar esse caso, foi desenvolvido uma nova solução para que aplicações possam se beneficiar destes tipos de consulta. Com esse novo recurso essa mesma consulta poderá ser escrita da seguinte forma:

DECLARE @NumeroPagina INT = 1

DECLARE @NumerosRegistrosPagina INT = 12

 

SELECT C.CustomerKey,

        C.CustomerAlternateKey,

        C.Title,

        C.FirstName + ‘ ‘ + ISNULL(C.MiddleName,) + ‘ ‘ + LastName AS Name,

        C.BirthDate,

        C.MaritalStatus,

        C.Gender,

        C.EmailAddress

FROM DimCustomer AS C

ORDER BY C.CustomerKey

OFFSET((@NumeroPagina 1) * @NumerosRegistrosPagina) ROWS

FETCH NEXT @NumerosRegistrosPagina ROWS ONLY;

Aonde logo após a ordenação dos dados utilizamos um recurso do cursor chamado OFFSET e FETCH NEXT. A cláusula OFFSET só pode ser utilizado se na consulta tiver ORDER BY, a função dele é andar entre as linhas a partir de uma quantidade que lhe é especificado. Logo após a variável @NumeroPagina conter a quantidade de numeros de páginas e @NumeroRegistrosPagina conter a quantidade total que será exibida, o FETCH NEXT possui o papel de andar para o próximo registro, desempenhando o mesmo papel do ROW_NUMBER() no caso acima.

Colocando as duas consultas uma do lado da outra temos o mesmo result set, porém planos de execução diferentes.

DECLARE @NumeroPagina INT = 1

DECLARE @NumerosRegistrosPagina INT = 12

 

;WITH DadosClientes AS

(

SELECT ROW_NUMBER() OVER(ORDER BY CustomerKey) AS RowNumberCustomerKey,

        C.CustomerKey,

        C.CustomerAlternateKey,

        C.Title,

        C.FirstName + ‘ ‘ + ISNULL(C.MiddleName,) + ‘ ‘ + LastName AS Name,

        C.BirthDate,

        C.MaritalStatus,

        C.Gender,

        C.EmailAddress

FROM DimCustomer AS C

)

SELECT CustomerKey, CustomerAlternateKey, Title, Name,

        BirthDate, MaritalStatus, Gender, EmailAddress

FROMDadosClientes

WHERE RowNumberCustomerKey BETWEEN(((@NumeroPagina 1) * @NumerosRegistrosPagina) + 1)

     AND(@NumeroPagina * @NumerosRegistrosPagina)

ORDER BY CustomerKey;

 

SELECT C.CustomerKey,

        C.CustomerAlternateKey,

        C.Title,

        C.FirstName + ‘ ‘ + ISNULL(C.MiddleName,) + ‘ ‘ + LastName AS Name,

        C.BirthDate,

        C.MaritalStatus,

        C.Gender,

        C.EmailAddress

FROM DimCustomer AS C

ORDER BY C.CustomerKey

OFFSET((@NumeroPagina 1) * @NumerosRegistrosPagina) ROWS

FETCH NEXT @NumerosRegistrosPagina ROWS ONLY;

image

(Figura 3 – Result Set ROW_NUMBER x OFFSET/Fetch Next )

image

(Figura 4 – Planos de Execução  ROW_NUMBER x OFFSET/Fetch Next )