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 !

SQL : Changer l'ordre d'éléments trié (avec des trous)

Mon besoin :
Je pars d'une liste d'éléments triés, mais je n'en n'affiche qu'une partie (les visibles) je veux faire monter ou descendre un élément dans la liste sans perdre l'ordre avec ceux non affichés.

Un exemple complet en SQL :

CREATE TABLE dbo.trimoi (
     tri int not null,
     nom varchar(50) not null,
     visible bit not null)

La colonne tri sert a trier la liste, la colonne nom identifie un élément de la liste et la colonne visible simule mon filtre d'affichage des éléments.

Un petit jeu de tests :
INSERT INTO dbo.trimoi (tri, nom, visible) 
     VALUES (1, 'Trois', 1),
            (2, 'second', 0),
            (3, 'premier', 1),
            (4, 'quatre', 1),
            (5, 'cinq', 1)

Les données en liste filtrée donnent :
SELECT * FROM dbo.trimoi WHERE visible = 1 ORDER BY tri

donne :
tri nom visible
1 Trois 1
3 premier 1
4 quatre 1
5 cinq 1

Imaginons que je veuille "monter" l'élément cinq d'un cran. Dans ce cas, il doit passer avant quatre.

Voici la procédure que je propose pour "monter un élément"
DECLARE @nom VARCHAR(50) = 'cinq'

DECLARE @pos INT
SELECT TOP 1 @pos = tri FROM dbo.trimoi WHERE visible = 1 
                                          AND tri < (SELECT tri FROM dbo.trimoi WHERE nom = @nom) ORDER BY tri DESC

IF (@pos IS NOT NULL)
BEGIN
  UPDATE dbo.trimoi SET tri = tri + 1 
  FROM dbo.trimoi
  WHERE tri >= @pos
    AND tri < (SELECT tri FROM dbo.trimoi WHERE nom = @nom)

  UPDATE dbo.trimoi SET tri = @pos WHERE nom = @nom
END 

L'exécution donne le bon résultat.
Si l'on réitère plusieurs fois l'exécution l'élément cinq monte progressivement, en sautant bien l'élément qui est masqué : second.

Quelques commentaires :
Le premier SELECT permet de trouver et d'affecter à la variable @pos la position finale de l'élément à monter.
On applique dans la requête le filtre qui nous intéresse (ici : visible = 1) ainsi que la recherche de l'élément tri le plus grand en dessous de l'élément 'cinq' (TOP 1 + ORDER BY tri DESC)

Si on a trouvé une position (cas ou cinq est en tête de liste) alors on fait les 2 updates :
1) Ajouter 1 à la colonne tri de tous les éléments entre @pos inclus et la position de l'élément cinq
2) La colonne tri de l'élément cinq prend la position @pos.

Pour être complet voici la procédure qui permet de redescendre 'cinq'
DECLARE @nomDesc VARCHAR(50) = 'cinq'

DECLARE @posDesc INT
SELECT TOP 1 @posDesc = tri FROM dbo.trimoi WHERE visible = 1 
                                              AND tri > (SELECT tri FROM dbo.trimoi WHERE nom = @nomDesc) ORDER BY tri ASC

IF (@posDesc IS NOT NULL)
BEGIN
  UPDATE dbo.trimoi SET tri = tri - 1 
  FROM dbo.trimoi
  WHERE tri <= @posDesc
    AND tri > (SELECT tri FROM dbo.trimoi WHERE nom = @nomDesc)

  UPDATE dbo.trimoi SET tri = @posDesc WHERE nom = @nomDesc
END 

Enjoy