SQL Pagination

Y-a-t-il des "Best Practices" pour paginer le résultat d'une requête ?
Si on a une requête du style :

SELECT xxx
FROM -- [... suite du FROM + WHERE très complexe ]

Et qu'on veut la paginer...
...Que faut il pour le faire correctement ?
 en entrée : la page à afficher, la taille des page et l'information de tri
 en sortie : il nous faut : le nombre total de pages, et ou le nombre total d'éléments et les X éléments seulement.
Le but aussi est de ne pas faire deux fois la requête : une pour compter et une pour renvoyer les X éléments demandés !!

Voila ce que je propose :

1) variables de pagination : @page est le numéro de la page demandée (commence à 1 !!)
@pageSize est le nombre de lignes dans la page (ici 20)

DECLARE @page INT = 1, @pageSize INT = 20

2) Entourer la requête de :

SELECT *, CEILING(x.nb_results / @pageSize) AS nb_pages
FROM
( 
   SELECT xxx
...

La colonne remontée nb_pages contiendra un entier avec le nombre total de pages possibles

3) Dans le SELECT ajouter les colonnes :
   
   , ROW_NUMBER() OVER(ORDER BY --- critère de tri ) AS rnk
  , COUNT(*) OVER() AS nb_results
   
   FROM -- [... suite du FROM + WHERE très complexe ]

La colonne rnk avec le ROW_NUMBER() permet d'attribuer un numéro à chaque ligne remontée
Pour ce faire il faut définir la ou les colonnes sur lesquelles trier
Remarque il n'est pas nécessaire de remonter cette colonne dans le résultat final.

La colonne nb_results avec le  COUNT(*) OVER() permet de calculer le nombre total de ligne, même si on filtres celle ci : Magique ! Merci SQL Server

4) à la fin de la requête on ajoute

) AS newTable
WHERE rnk BETWEEN ((@page - 1) * @pageSize + 1) AND (@page * @pageSize)

C'est ici qu'on prélève les lignes qui correspondent à la page à visualiser.


Enjoy !