segunda-feira, 2 de abril de 2012

Paginação - SQL Server (row_number e rowcount)

Pesquisando formas de fazer paginação no SQL Server achei algumas coisas interessantes, mas o que mais me agradou foi a paginação com a utilização do comando row_number, por não utilizar o "not in" e nem tabela temporária.

O comando row_number foi criado no SQL2005, se a versão em que você está desenvolvendo for inferior à esta, existem soluções que utilizam o rowcount, que também parece bastante eficiente, porém requer uma única coluna de ordenação (o que geralmente mata qualquer implementação), mas segue um link que pode servir para alguns casos:

Exemplo utilizando o rowcount: http://www.4guysfromrolla.com/webtech/042606-1.shtml
Obs: - RowCount requer uma única coluna de ordenação.

Utilizando o row_number para paginação:

create procedure listar(
    @cidade varchar(50),
    @pagina int,
    @tamanhopagina int)
as
begin

declare @TotalRows int
-- Fazer o count(*) é mais rápido do que usar o Count(*) OVER() dentro da próxima
-- query.
select @TotalRows = COUNT(*) from cliente where cidade = @cidade

-- Seleciona a quantidade mxima de registros por pgina (@tamanhopagina).
Select Top(@tamanhopagina) * from
(
    Select
    -- campo utilizado na paginação (informar a ordenação dos campos aqui).
    RowID=ROW_NUMBER() OVER (ORDER BY codigoempresa, codigocliente),
    -- retorna a quantidade de registros total da query.
    @TotalRows as TotalRows, -- Count(*) OVER() é mais lento e utiliza mais páginas de
                                                  -- dados do que usar o count(*) em outra query.
    -- Calcula a quantidade de páginas de acordo com o número de
    -- registros e a quantidade de registros por página.
    @TotalRows / @tamanhopagina + case
                                                             when @TotalRows % @tamanhopagina > 0
                                                             then 1
                                                             else 0
                                                             end QtdePaginas,
    -- evitar usar o * para melhoria na performance, retorne somente os campos que for utilizar.
    *
    from cliente
    where cidade=@cidade
) lista
-- Faz a conta básica ((@pagina-1)*@tamanhopagina) para retornar somente os
-- registros corretos de acordo com número da página e o tamanho da página,
-- utilizando o campo gerado pelo row_number() over (), nomeado aqui de RowId.
Where lista.RowId > ((@pagina-1)*@tamanhopagina)

end

Nenhum comentário:

Postar um comentário