paginação é muitas vezes usado em aplicações onde o usuário pode clicar em Anterior / Próximo para navegar as páginas que compõem os resultados, ou clicar em um número de página para ir diretamente para uma página específica.
Ao executar consultas em SQL Server, você pode paginar os resultados usando o OFFSET e FETCH argumentos de ORDER BY cláusula. Estes argumentos foram introduzidos no SQL Server 2012, portanto você pode usar esta técnica se você tiver SQL Server 2012 ou superior.
neste contexto, a paginação é onde você divide os resultados da pesquisa em blocos menores, cada bloco continuando onde o anterior terminou. Por exemplo, se uma consulta retorna 1000 linhas, você pode paginá-las para que elas sejam devolvidas em grupos de 100. Um aplicativo pode passar o número da página e o tamanho da página para o servidor SQL, e o servidor SQL pode então usá-lo para devolver apenas os dados para a página solicitada.
Exemplo 1 – Sem Paginação
Primeiro, vamos executar uma consulta que retorna todas as linhas em uma tabela:
SELECT *FROM GenresORDER BY GenreId;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
este exemplo usa nenhuma paginação – todos os resultados são apresentados.
Este conjunto de resultados é tão pequeno que normalmente não requer paginação, mas para efeitos deste artigo, vamos paginá-lo.
Exemplo 2 – Apresentar os 3 Primeiros Resultados
Este exemplo apresenta a primeira de três resultados:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
neste caso, eu especificar que os resultados devem começar no primeiro resultado e apresentar os próximos três linhas. Isto é feito usando o seguinte:
-
OFFSET 0 ROWSespecifica que não deve haver deslocamento (um deslocamento de zero). -
FETCH NEXT 3 ROWS ONLYobtém as três linhas seguintes do deslocamento. Uma vez que eu especificei um deslocamento de zero, as três primeiras linhas são obtidas.
Se tudo o que queríamos era os 3 melhores resultados, poderíamos ter alcançado o mesmo resultado usando a cláusula TOP em vez de especificar os valores de deslocamento e obtenção. No entanto, isto não nos teria permitido fazer a próxima parte.
Exemplo 3 – Exibir os Próximos 3 Resultados
Agora vamos exibir a três resultados:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Então, a única coisa que eu mudei foi o deslocamento.
os valores de deslocamento e obtenção também podem ser uma expressão fornecida como uma variável, parâmetro, ou subquery escalar constante. Quando um subquery é usado, ele não pode referenciar quaisquer colunas definidas no escopo da consulta externa (ele não pode ser correlacionado com a consulta externa).
os exemplos a seguir usam expressões para mostrar duas abordagens para paginar os resultados.
exemplo 4-paginação pelo número da linha
este exemplo usa expressões para indicar o número da linha a começar.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Aqui, eu uso @StartRow int = 1 para especificar que os resultados devem começar na primeira linha.
Aqui está o que acontece se eu aumentar esse valor para 2.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
começa na segunda fileira. Usando este método, posso especificar a linha exata para começar.
exemplo 5-paginação pelo número da Página
este exemplo é quase idêntico ao exemplo anterior, exceto que permite especificar o número da página, em oposição ao número da linha.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Então, o primeiro resultado é o mesmo. No entanto, vamos ver o que acontece quando aumentamos @PageNumber para 2 (renomeei esta variável para refletir seu novo propósito).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
desta vez, os resultados começam na quarta linha. Então usando este método você pode simplesmente passar o número da página em vez do número da linha.
Exemplo 6 – Paginação Loop
Para terminar, aqui está um exemplo rápido que percorre todas as páginas e especifica o número da linha inicial para cada iteração:
DECLARE @StartRow int = 1, @RowsPerPage int = 3;WHILE (SELECT COUNT(*) FROM Genres) >= @StartRow BEGIN SELECT * FROM Genres ORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;SET @StartRow = @StartRow + @RowsPerPage; CONTINUEEND;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+(3 rows affected)+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+(3 rows affected)+-----------+---------+| GenreId | Genre ||-----------+---------|| 7 | Rap || 8 | Punk |+-----------+---------+(2 rows affected)
Exemplo 7 – LINHA vs LINHAS
Se você encontrar o código que usa o ROW em vez de ROWS, ambos os argumentos fazer a mesma coisa. Eles são sinônimos e são fornecidos para compatibilidade ANSI.
Aqui está o primeiro exemplo nesta página, mas com ROW em vez de ROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
Resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Exemplo 8 – PRIMEIRO vs SEGUINTE
O mesmo se aplica para FIRST e NEXT. Estes são sinónimos fornecidos para compatibilidade ANSI.
Aqui está o exemplo anterior, mas com FIRST em vez de NEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
resultado:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+