Pagination des résultats sous SQL Server

Pour effectuer une requête SQL paginée, PostgreSQL possède les clauses LIMIT et OFFSET tandis que MySQL utilise la syntaxe LIMIT x,y. Étonnamment, Microsoft SQL Server n'avait pas d'équivalent, jusqu'à l'arrivée de la version MSSQL 2005 et plus récemment la 2008.

Ces dernières versions ont introduit une nouvelle approche pour construire la requête, qui ne ressemble en rien à ce que PgSQL ou MySQL font. La magie s'opère grâce à la fonction row_number(), qui permet de numéroter séquentiellement les résultats d'une requête. À titre d'exemple, imaginez qu'on définisse préalablement une sous-requête qui liste des villes. On utilisera le mot clé OVER pour associer un numéro de 1 à n, en suivant l'ordre spécifié par le champ noté entre parenthèses (dans mon exemple, alphabétique selon le nom de la ville). Une fois la sous-requête définie, il ne restera qu'à l'utiliser dans un autre SELECT et utiliser la référence à l'indice idx attribué par row_number() pour trancher les résultats selon le nombre d'enregistrements par page voulu.

WITH subquery AS
(
SELECT *, row_number() OVER (ORDER BY city_name) as idx
FROM dbo.table_cities
)
SELECT *
FROM subquery
WHERE idx BETWEEN 50 AND 100;

Personnellement, je suis d'accord que c'est pratique et que ça constitue un gros pas en avant pour SQL Server, mais c'est beaucoup moins élégant que ce que propose ses concurrents. OK, je sais que ça fera plaisir à plusieurs de l'entendre : Microsoft a copié sa solution sur Oracle :-)

Précédemment, que devait-on faire lorsqu'on utilisait MSDE (Desktop Engine) ou SQL Server 2000 ? Il fallait simplement user d'imagination. Mais on peut gagner beaucoup à apprendre quel genre de mécanisme permettait d'arriver à paginer des résultats.

On pouvait créer une requête avec des sous-requêtes imbriquées mais ma solution favorite était d'utiliser du Transact-SQL (T-SQL) à l'intérieur d'une procédure stockée. À l'aide d'une table temporaire ou une variable table qui contient une colonne integer avec IDENTITY(1,1) - qui débute par 1 (seed) et qui s'incrémente de 1 à chaque appel, on insère les résultats de la requête de base et un numéro incrémentiel unique est attribué à chaque enregistrement, ce qui nous permet ensuite d'extraire la page.

CREATE PROCEDURE 
@page INT,
@resultsPerPage INT
AS
DECLARE
@tempResults TABLE (
idx INT IDENTITY(1,1),
field1 INT,
field2 VARCHAR(100),
field3 BIT,
...
)
DECLARE
@idxFirst INT,
@idxLast INT

-- assigner un numéro de ligne automatiquement
INSERT INTO @tempResults (field1, field2, field3)
SELECT field1, field2, field3 FROM dbo.my_table
WHERE condition1 = x
AND condition2 = y
ORDER BY field2

-- pagination
/*
Selon ce calcul, si on compte 50 résultats par page
Page 1 : 1 à 50
Page 2 : 51 à 100
*/
SET @idxFirst = (@page - 1) * @resultsPerPage + 1
SET @idxLast = @idxFirst + @resultsPerPage - 1

-- retourner la page
SELECT * FROM @tempResults
WHERE idx BETWEEN @idxFirst AND @idxLast
GO

Vous devrez par la suite vous assurer que le numéro de page n'est pas négatif, que la page demandée n'excède pas le nombre de résultats, que votre table de base ne possède pas trop d'enregistrements, ce qui entraînerait une perte de performance. De quoi se faire aller les méninges un peu. À bien y penser, row_number() n'est pas si mal.