La pagination est souvent utilisée dans les applications où l’utilisateur peut cliquer sur Précédent / Suivant pour naviguer dans les pages qui composent les résultats, ou cliquer sur un numéro de page pour accéder directement à une page spécifique.
Lors de l’exécution de requêtes dans SQL Server, vous pouvez paginer les résultats en utilisant les arguments OFFSET et FETCH de la clause ORDER BY. Ces arguments ont été introduits dans SQL Server 2012, vous pouvez donc utiliser cette technique si vous avez SQL Server 2012 ou une version ultérieure.
Dans ce contexte, la pagination est l’endroit où vous divisez les résultats de la requête en petits morceaux, chaque morceau continuant là où le précédent s’est terminé. Par exemple, si une requête renvoie 1000 lignes, vous pouvez les paginer afin qu’elles soient renvoyées par groupes de 100. Une application peut transmettre le numéro de page et la taille de la page à SQL Server, et SQL Server peut ensuite l’utiliser pour renvoyer uniquement les données de la page demandée.
Exemple 1 – Pas de pagination
Tout d’abord, exécutons une requête qui renvoie toutes les lignes d’une table :
SELECT *FROM GenresORDER BY GenreId;
Résultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country || 4 | Pop || 5 | Blues || 6 | Hip Hop || 7 | Rap || 8 | Punk |+-----------+---------+
Cet exemple n’utilise aucune pagination – tous les résultats sont affichés.
Cet ensemble de résultats est si petit qu’il ne nécessiterait normalement pas de pagination, mais pour les besoins de cet article, paginons-le.
Exemple 2 – Afficher les 3 Premiers résultats
Cet exemple affiche les trois premiers résultats:
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Résultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Dans ce cas, je précise que les résultats doivent commencer au premier résultat et afficher les trois lignes suivantes. Ceci est fait en utilisant ce qui suit:
-
OFFSET 0 ROWSspécifie qu’il ne doit pas y avoir de décalage (un décalage de zéro). -
FETCH NEXT 3 ROWS ONLYobtient les trois lignes suivantes à partir du décalage. Puisque j’ai spécifié un décalage de zéro, les trois premières lignes sont récupérées.
Si tout ce que nous voulions était les 3 meilleurs résultats, nous aurions pu obtenir le même résultat en utilisant la clause TOP au lieu de spécifier les valeurs de décalage et de récupération. Cependant, cela ne nous aurait pas permis de faire la partie suivante.
Exemple 3 – Afficher les 3 résultats suivants
Maintenant, affichons les trois résultats suivants:
SELECT *FROM GenresORDER BY GenreId OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
Résultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Donc la seule chose que j’ai modifiée était le décalage.
Les valeurs de décalage et de récupération peuvent également être une expression fournie sous forme de variable, de paramètre ou de sous-requête scalaire constante. Lorsqu’une sous-requête est utilisée, elle ne peut référencer aucune colonne définie dans la portée de la requête externe (elle ne peut pas être corrélée avec la requête externe).
Les exemples suivants utilisent des expressions pour montrer deux approches pour paginer les résultats.
Exemple 4 – Pagination par numéro de ligne
Cet exemple utilise des expressions pour spécifier le numéro de ligne à partir duquel commencer.
DECLARE @StartRow int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Résultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Ici, j’utilise @StartRow int = 1 pour spécifier que les résultats doivent commencer à la première ligne.
Voici ce qui se passe si j’incrémente cette valeur en 2.
DECLARE @StartRow int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET @StartRow - 1 ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Résultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 2 | Jazz || 3 | Country || 4 | Pop |+-----------+---------+
Cela commence à la deuxième ligne. En utilisant cette méthode, je peux spécifier la ligne exacte à partir de laquelle commencer.
Exemple 5 – Pagination par Numéro de page
Cet exemple est presque identique à l’exemple précédent, sauf qu’il permet de spécifier le numéro de page, par opposition au numéro de ligne.
DECLARE @PageNumber int = 1, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Résultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Donc le premier résultat est le même. Cependant, voyons ce qui se passe lorsque nous incrémentons @PageNumber en 2 (j’ai renommé cette variable pour refléter son nouvel objectif).
DECLARE @PageNumber int = 2, @RowsPerPage int = 3; SELECT * FROM GenresORDER BY GenreId ASC OFFSET (@PageNumber - 1) * @RowsPerPage ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
Résultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 4 | Pop || 5 | Blues || 6 | Hip Hop |+-----------+---------+
Cette fois, les résultats commencent à la quatrième ligne. Ainsi, en utilisant cette méthode, vous pouvez simplement passer le numéro de page plutôt que le numéro de ligne.
Exemple 6 – Boucle de pagination
Pour terminer, voici un exemple rapide qui parcourt toutes les pages et spécifie le numéro de ligne de départ pour chaque itération :
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;
Résultat :
+-----------+---------+| 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)
Exemple 7 – LIGNE vs LIGNES
Si vous rencontrez code qui utilise ROW au lieu de ROWS, les deux arguments font la même chose. Ils sont synonymes et sont fournis pour la compatibilité ANSI.
Voici le premier exemple sur cette page, mais avec ROW au lieu de ROWS.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH NEXT 3 ROW ONLY;
Résultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+
Exemple 8 – PREMIER vs SUIVANT
La même chose s’applique à FIRST et NEXT. Ce sont des synonymes fournis pour la compatibilité ANSI.
Voici l’exemple précédent mais avec FIRST au lieu de NEXT.
SELECT *FROM GenresORDER BY GenreId OFFSET 0 ROW FETCH FIRST 3 ROW ONLY;
Résultat:
+-----------+---------+| GenreId | Genre ||-----------+---------|| 1 | Rock || 2 | Jazz || 3 | Country |+-----------+---------+