Paginação é um processo que é usado para dividir um dado grande em páginas discretas menores, e este processo também é conhecido como paginação. A paginação é comumente usada por aplicações web e pode ser vista no Google. Quando pesquisamos algo no Google, ele mostra os resultados na página separada; esta é a idéia principal da paginação.
Agora, discutiremos como conseguir paginação no SQL Server nas próximas partes do artigo.
Preparar dados de amostra
Antes de começarmos a entrar em detalhes sobre paginação, criaremos uma tabela de amostra e a preencheremos com alguns dados sintéticos. Na consulta seguinte, vamos criar uma tabela de amostras de fruta que armazena nomes de frutas e preços de venda. Na próxima parte do artigo, vamos utilizar esta tabela.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
QUADRO DE CRIAÇÃO Amostra de Frutos (
IDENTIFICAÇÃO DA CHAVE PRIMÁRIA INT(1,1) ,
FruitName VARCHAR(50) ,
Preço INT
)
GO
INSERIR VALORES DE Amostras de Frutas(‘Maçã’,20)
INSERIR VALORES DE Amostras de Frutas(‘Damasco’,12)
INSERIR VALORES DE Amostras de Frutas(‘Banana’,8)
INSERIR VALORES DE Amostras de Frutas(‘Cereja’,11)
INSERIR VALORES DE Amostras de Frutas(‘Morango’,26)
INSERIR VALORES de Frutas(‘Limão’,4)
INSERIR VALORES de Frutas(‘Kiwi’,14)
INSERIR VALORES DE Amostras de Frutas(‘Coco’,34)
INSERIR VALORES DE Amostras de Frutas(‘Laranja’,24)
INSERIR VALORES DE Amostras de Frutas(‘Framboesa’,13)
INSERIR VALORES DE Amostras de Frutas(‘Manga’,9)
INSERIR VALORES DE Amostras de Frutas(‘Mandarim’,19)
INSERIR VALORES DE Amostras de Frutas(‘Abacaxi’,22)
GO
SELECCIONAR * DA AmostraFrutas
> |
O que é Paginação no SQL Server?
Em termos do SQL Server, o objectivo da paginação é, dividir um conjunto de resultados em páginas discretas com a ajuda da consulta. Quando os argumentos OFFSET e FETCH são usados com a cláusula ORDER BY numa instrução SELECT, será uma solução de paginação para o SQL Server.
OFFSET especifica quantas linhas serão puladas do conjunto de resultados da consulta. No exemplo a seguir, a consulta saltará as 3 primeiras linhas da tabela SampleFruits e então retornará todas as linhas restantes.
|
1
2
3
4
|
SELECT FruitName, Preço
FROM AmostraFrutas
ORDEM POR Preço
OFFSET 3 ROWS
|
Quando definimos o valor OFFSET como 0, nenhuma linha será saltada do conjunto de resultados. A seguinte consulta pode ser um exemplo deste tipo de utilização:
|
1
2
3
|
SELECT FruitName,Preço DA AmostraFrutas
ORDEM POR Preço
OFFSET 0 ROWS
|
Por outro lado, se definirmos o valor OFFSET, que é maior do que o número total de linhas do conjunto de resultados, não serão apresentadas linhas no resultado. Quando consideramos a seguinte consulta, o número total de linhas da tabela SampleFruits é 13, e definimos o valor OFFSET como 20, pelo que a consulta não irá apresentar qualquer resultado.
|
1
2
3
|
SELECT FruitName,Preço DA AmostraFruits
ORDEM POR Preço
OFFSET 20 ROWS
|
FETCH argumento especifica quantas linhas serão exibidas no resultado, e o argumento FETCH deve ser usado com o argumento OFFSET. No exemplo seguinte, vamos saltar as 5 primeiras linhas e depois limitar o conjunto de resultados a 6 linhas para a nossa tabela de amostras.
|
1
2
3
4
|
SELECT FruitName, Preço
DE AmostraFrutas
ORDEM POR Preço
OFFSET 5 ROWS FETCH NEXT 6 ROWS APENAS
|
Tip: A TOP CLAUSE limita o número de filas que retornaram da declaração SELECT. Quando usamos a cláusula TOP sem ORDER BY, ela pode ser retornada para resultados arbitrários. Quando consideramos o seguinte exemplo, ela retornará 3 linhas aleatórias em cada execução da consulta.
|
1
2
|
SELECT TOP 7 FruitName, Preço
FROM SampleFruits
|
Como aprendemos, o argumento OFFSET-FETCH requer a cláusula ORDER BY na declaração SELECT. Se quisermos implementar uma ordem indefinida que goste da utilização anterior da cláusula TOP com argumentos OFFSET-FETCH, podemos utilizar uma consulta que se parece com a seguinte:
|
1
2
3
> |
SELECT FruitName ,Preço DA AmostraFrutas
ORDEM POR (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS APENAS
|
Pagination query in SQL Server
Após descobrir a resposta para “O que é Paginação?”, vamos aprender como podemos escrever uma consulta de paginação no SQL Server. A princípio, executaremos a seguinte consulta e abordaremos a consulta:
|
1
2
3
4
5
6
7
8
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
SET @PageNumber=2
SET @RowsOfPage=4
SELECT FruitName,Preço DA AmostraFrutas
ORDEM POR PREÇO
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Como podemos ver, declaramos duas variáveis na consulta acima, e essas variáveis são:
- @PageNumber – Especifica o número da página que será exibida
- @RowsOfPage – Especifica quantos números de linhas serão exibidos na página. Como resultado, a instrução SELECT exibe a segunda página, que contém 4 linhas
Dynamic Sorting with Pagination
Aplicações podem precisar ordenar os dados de acordo com diferentes colunas, em ordem ascendente ou descendente ao lado da paginação. Para superar este tipo de requisito, podemos usar uma cláusula ORDER BY com condições CASE para que possamos obter uma consulta que possa ser ordenada pelas variáveis. A seguinte consulta pode ser um exemplo deste tipo de uso:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @SortingCol AS VARCHAR(100) =’FruitName’
DECLARE @SortType AS VARCHAR(100) = ‘DESC’
SET @PageNumber=1
SET @RowsOfPage=4
SELECT FruitName,Preço DA AmostraFrutas
ORDEM POR
CASO QUANDO @SortingCol = ‘Preço’ E @SortType =’ASC’ ENTÃO Preço FIM ,
CASO QUANDO @SortingCol = ‘Price’ AND @SortType =’DESC’ THEN Price END DESC,
CASO QUANDO @SortingCol = ‘FruitName’ AND @SortType =’ASC’ THEN FruitName END ,
CASO QUANDO @SortingCol = ‘FruitName’ E @SortType =’DESC’ ENTÃO FruitName END DESC
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Also, podemos alterar a coluna de ordenação e a direção de ordenação através das variáveis para a consulta acima.
Paginação em um Loop
Neste exemplo, vamos aprender uma técnica de consulta que retorna todos os resultados discretos da página com uma única consulta.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
>
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @MaxTablePage AS FLOAT
SET @PageNumber=1
SET @RowsOfPage=4
SELECT @MaxTablePage = COUNT(*) FROM SampleFruits
SET @MaxTablePage = CEILING(@MaxTablePage/@RowsOfPage)
WHILE @MaxTablePage >= @PageNumber
BEGIN
SELECT FruitName,Preço DA AmostraFrutas
ORDEM POR PREÇO
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
SET @PageNumber = @PageNumber + 1
END
|
Para esta consulta, nós criámos uma fórmula muito simples. No início, atribuímos o número total de linhas da tabela SampleFruit à variável @MaxTablePage, e depois dividimo-la em quantas linhas serão exibidas em uma página. Então, calculamos o número de páginas que serão exibidas. Entretanto, o valor calculado pode ser decimal, e para isso, usamos a função CEILING para arredondá-lo para o menor número inteiro que for maior do que o número calculado. Como segundo passo, implementamos uma variável WHILE-LOOP e iteramos @PageNumber até a última página do número.
Conclusion
Neste artigo, tentamos encontrar a resposta para a pergunta “O que é Paginação?”, particularmente para o SQL Server. Os argumentos OFFSET-FETCH ajudam a implementar quantas linhas queremos saltar e quantas linhas queremos mostrar no conjunto de resultados quando as utilizamos com a cláusula ORDER BY nas instruções SELECT. E finalmente, aprendemos como podemos conseguir paginação no SQL Server com estes argumentos.
- Autor
- Recent Posts
A maior parte de sua carreira tem sido focada em Administração e Desenvolvimento de Banco de Dados do SQL Server. Seus interesses atuais estão na administração de banco de dados e Business Intelligence. Você pode encontrá-lo no LinkedIn.
Ver todos os posts por Esat Erkec
- Mitos de Otimização de Consulta – 23 de Março, 2021
- Sintomas do cheiro do parâmetro no SQL Server – 17 de março de 2021
- Usando a correção automática do plano para ajuste de consulta – 4 de março de 2021