La pagination est un processus qui est utilisé pour diviser une grande donnée en petites pages discrètes, et ce processus est également connu sous le nom de pagination. La pagination est couramment utilisée par les applications web et peut être vue sur Google. Lorsque nous recherchons quelque chose sur Google, il montre les résultats sur la page séparée ; c’est l’idée principale de la pagination.
Maintenant, nous allons discuter de la façon de réaliser la pagination dans SQL Server dans les prochaines parties de l’article.
Préparation des données d’échantillon
Avant de commencer à entrer dans le détail de la pagination, nous allons créer une table d’échantillon et la remplir de quelques données synthétiques. Dans la requête suivante, nous allons créer une table SampleFruits qui stocke les noms des fruits et leurs prix de vente. Dans la suite de l’article, nous utiliserons cette table.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
CREATE TABLE SampleFruits (
Id INT PRIMARY KEY IDENTITY(1,1) ,
Nom du fruit VARCHAR(50) ,
Prix INT
)
GO
INSERT INTO SampleFruits VALUES(‘Apple’,20)
INSERT INTO SampleFruits VALUES(‘Abricot’,12)
INSERT INTO SampleFruits VALUES(‘Banane’,8)
INSERT INTO SampleFruits VALUES(‘Cherry’,11)
INSERT INTO SampleFruits VALUES(‘Strawberry’,26)
INSERT INTO SampleFruits VALUES(‘Lemon’,4)
INSERT INTO SampleFruits VALUES(‘Kiwi’,14)
INSERT INTO SampleFruits VALUES(‘Coconut’,34)
INSERT INTO SampleFruits VALUES(‘Orange’,24)
INSERT INTO SampleFruits VALUES(‘Raspberry’,13)
INSERT INTO SampleFruits VALUES(‘Mango’,9)
INSERT INTO SampleFruits VALUES(‘Mandarin’,19)
INSERT INTO SampleFruits VALUES(‘Ananas’,22)
GO
SELECT * FROM SampleFruits
|
Qu’est-ce que la pagination dans SQL Server ?
En termes de SQL Server, l’objectif de la pagination est, la division d’un ensemble de résultats en pages discrètes avec l’aide de la requête. Lorsque les arguments OFFSET et FETCH sont utilisés avec la clause ORDER BY dans une instruction SELECT, il s’agira d’une solution de pagination pour SQL Server.
L’argument OFFSET spécifie combien de lignes seront sautées du jeu de résultats de la requête. Dans l’exemple suivant, la requête sautera les 3 premières lignes de la table SampleFruits et retournera ensuite toutes les lignes restantes.
|
1
2
3
4
|
SELECT FruitName, Prix
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS
|
Lorsque nous définissons la valeur OFFSET à 0, aucune ligne ne sera sautée du jeu de résultats. La requête suivante peut être un exemple de ce type d’utilisation :
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
D’autre part, si nous définissons la valeur OFFSET, qui est supérieure au nombre total de lignes du jeu de résultats, aucune ligne ne sera affichée sur le résultat. Lorsque nous considérons la requête suivante, le nombre total de lignes de la table SampleFruits est de 13, et nous avons défini la valeur OFFSET à 20, donc la requête n’affichera aucun résultat.
|
1
2
3
|
SELECT FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ROWS
|
L’argument FETCH spécifie combien de lignes seront affichées dans le résultat, et l’argument FETCH doit être utilisé avec l’argument OFFSET. Dans l’exemple suivant, nous allons sauter les 5 premières lignes, puis limiter le jeu de résultats à 6 lignes pour notre tableau d’échantillons.
|
1
2
3
4
|
SELECT FruitName, Prix
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tip : La clause TOP limite le nombre de lignes retournées par l’instruction SELECT. Lorsque nous utilisons la clause TOP sans ORDER BY, il peut être renvoyé à des résultats arbitraires. Lorsque nous considérons l’exemple suivant, il retournera 3 lignes aléatoires à chaque exécution de la requête.
|
1
2
|
SELECT TOP 7 FruitName, Prix
FROM SampleFruits
|
Comme nous l’avons appris, l’argument OFFSET-FETCH nécessite la clause ORDER BY dans l’instruction SELECT. Si nous voulons mettre en œuvre un ordre indéfini qui aime l’utilisation précédente de la clause TOP avec les arguments OFFSET-FETCH, nous pouvons utiliser une requête qui ressemble à celle ci-dessous :
|
1
2
3
|
SELECT FruitName ,Price FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
.
Requête de pagination dans SQL Server
Après avoir trouvé la réponse à la question » Qu’est-ce que la pagination ? », nous allons apprendre comment nous pouvons écrire une requête de pagination dans SQL Server. Dans un premier temps, nous allons exécuter la requête suivante et nous attaquer à la requête :
|
1
2
3
4
5
6
7
8
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
SET @PageNumber=2
SET @RowsOfPage=4
SELECT FruitName,Prix de SampleFruits
ORDER BY Price
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Comme nous pouvons le voir, nous avons déclaré deux variables dans la requête ci-dessus, et ces variables sont :
- @PageNumber – Elle spécifie le numéro de la page qui sera affichée
- @RowsOfPage – Elle spécifie combien de numéros de lignes seront affichés sur la page. En conséquence, l’instruction SELECT affiche la deuxième page, qui contient 4 lignes
Tri dynamique avec pagination
Les applications peuvent avoir besoin de trier les données selon différentes colonnes, soit dans l’ordre ascendant ou descendant à côté de la pagination. Pour pallier à ce type d’exigence, nous pouvons utiliser une clause ORDER BY avec des conditions CASE afin d’obtenir une requête qui peut être triée par les variables. La requête suivante peut être un exemple de ce type d’utilisation :
|
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,Price FROM SampleFruits
ORDER BY
CASE WHEN @SortingCol = ‘Price’ AND @SortType = ‘ASC’ THEN Price END ,
CASE WHEN @SortingCol = ‘Price’ AND @SortType = ‘DESC’ THEN Price END DESC,
CASE WHEN @SortingCol = ‘FruitName’ AND @SortType = ‘ASC’ THEN FruitName END ,
CASE WHEN @SortingCol = ‘FruitName’ AND @SortType = ‘DESC’ THEN FruitName END DESC
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
Aussi, nous pouvons changer la colonne de tri et la direction du tri grâce aux variables de la requête ci-dessus.
Pagination en boucle
Dans cet exemple, nous allons apprendre une technique de requête qui renvoie tous les résultats de pages discrètes avec une seule requête.
|
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,Prix de SampleFruits
ORDER BY Price
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
SET @PageNumber = @PageNumber + 1
END
|
Pour cette requête, nous avons créé une formule assez simple. Dans un premier temps, nous avons attribué le nombre total de lignes de la table SampleFruit à la variable @MaxTablePage, puis nous l’avons divisé en fonction du nombre de lignes qui seront affichées sur une page. Nous avons donc calculé le nombre de pages qui seront affichées. Cependant, la valeur calculée peut être une décimale, et pour cela, nous avons utilisé la fonction CEILING pour l’arrondir au plus petit nombre entier supérieur au nombre calculé. Dans un deuxième temps, nous avons mis en œuvre un WHILE-LOOP et itéré la variable @PageNumber jusqu’à la dernière page du numéro.
Conclusion
Dans cet article, nous avons essayé de trouver la réponse à la question « Qu’est-ce que la pagination ? », notamment pour SQL Server. Les arguments OFFSET-FETCH aident à mettre en œuvre le nombre de lignes que nous voulons sauter et le nombre de lignes que nous voulons afficher dans le jeu de résultats lorsque nous les utilisons avec la clause ORDER BY dans les instructions SELECT. Et enfin, nous avons appris comment nous pouvons réaliser la pagination dans SQL Server avec ces arguments.
- Auteur
- Postages récents
La majeure partie de sa carrière a été axée sur l’administration et le développement de bases de données SQL Server. Il s’intéresse actuellement à l’administration des bases de données et à la Business Intelligence. Vous pouvez le trouver sur LinkedIn.
Voir tous les messages de Esat Erkec
- Mythes d’optimisation des requêtes – 23 mars, 2021
- Symptômes de reniflage de paramètres dans SQL Server – 17 mars 2021
- Utilisation de la correction automatique de plan pour l’optimisation des requêtes – 4 mars 2021
.