Microsoft SQL Server => Pagination

Introduction

Décalage de lignes et pagination dans différentes versions de SQL Server

Syntaxe

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS SEULEMENT;

Pagination utilisant ROW_NUMBER avec une expression de table commune

SQL Server 2008

La fonction ROW_NUMBER peut attribuer un numéro incrémentiel à chaque ligne d'un jeu de résultats. Combiné à une expression de table commune qui utilise un opérateur BETWEEN , il est possible de créer des "pages" de jeux de résultats. Par exemple: la première page contenant les résultats 1-10, la deuxième page contenant les résultats 11-20, la troisième page contenant les résultats 21-30, etc.

WITH data
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY name) AS row_id,
        object_id,
        name, 
        type,
        create_date
    FROM sys.objects
)
SELECT *
FROM data
WHERE row_id BETWEEN 41 AND 50

Note: Il n'est pas possible d'utiliser ROW_NUMBER dans une clause WHERE comme:

SELECT object_id,
    name,
    type,
    create_date
FROM sys.objects
WHERE ROW_NUMBER() OVER (ORDER BY name) BETWEEN 41 AND 50

Bien que cela soit plus pratique, SQL Server renverra l'erreur suivante dans ce cas:

Msg 4108, niveau 15, état 1, ligne 6
Les fonctions fenêtrées ne peuvent apparaître que dans les clauses SELECT ou ORDER BY.

Pagination avec OFFSET FETCH

SQL Server 2012

La clause OFFSET FETCH implémente la pagination de manière plus concise. Avec elle, il est possible de sauter les lignes N1 (spécifiées dans OFFSET ) et de renvoyer les N2 lignes suivantes (spécifiées dans FETCH ):

SELECT *
FROM sys.objects
ORDER BY object_id
OFFSET 40 ROWS FETCH NEXT 10 ROWS ONLY

La clause ORDER BY est requise pour fournir des résultats déterministes.

Paginaton avec requête interne

Dans les versions antérieures de SQL Server, les développeurs devaient utiliser le double tri associé au mot clé TOP pour renvoyer des lignes dans une page:

 SELECT TOP 10 *
 FROM
 (
    SELECT
    TOP 50 object_id,
        name,
        type,
        create_date
    FROM sys.objects
    ORDER BY name ASC
) AS data
ORDER BY name DESC

La requête interne renverra les 50 premières lignes classées par name . Ensuite, la requête externe inversera l'ordre de ces 50 lignes et sélectionnera les 10 premières lignes (il s'agira des 10 dernières lignes du groupe avant l'inversion).

Paging dans différentes versions de SQL Server

SQL Server 2012/2014

DECLARE @RowsPerPage INT = 10, @PageNumber INT = 4
SELECT OrderId, ProductId
FROM OrderDetail
ORDER BY OrderId
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

SQL Server 2005/2008 / R2

DECLARE @RowsPerPage INT = 10, @PageNumber INT = 4
SELECT OrderId, ProductId
FROM (
    SELECT OrderId, ProductId, ROW_NUMBER() OVER (ORDER BY OrderId) AS RowNum
    FROM OrderDetail) AS OD
WHERE OD.RowNum BETWEEN ((@PageNumber - 1 ) * @RowsPerPage) + 1
AND @RowsPerPage * @PageNumber

SQL Server 2000

DECLARE @RowsPerPage INT = 10, @PageNumber INT = 4
SELECT OrderId, ProductId
FROM (SELECT TOP (@RowsPerPage) OrderId, ProductId
        FROM (SELECT TOP ((@PageNumber)*@RowsPerPage) OrderId, ProductId
                FROM OrderDetail
                ORDER BY OrderId) AS OD
    ORDER BY OrderId DESC) AS OD2
ORDER BY OrderId ASC

SQL Server 2012/2014 utilisant ORDER BY OFFSET et FETCH NEXT

Pour obtenir les 10 lignes suivantes, lancez cette requête:

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Points clés à considérer lors de son utilisation:

ORDER BY est obligatoire pour utiliser la clause OFFSET et FETCH .
OFFSET clause OFFSET est obligatoire avec FETCH . Vous ne pouvez jamais utiliser, ORDER BY … FETCH .
TOP ne peut pas être combiné avec OFFSET et FETCH dans la même expression de requête.

Modified text is an extract of the original Stack Overflow Documentation

Sous licence CC BY-SA 3.0

Non affilié à Stack Overflow