Mim

Pagination avec SQL Server 2005 : CTE, Row_Number

Aujourd'hui petite astuce pour faire de la pagination de données avec SQL Server 2005 :

Vous avez suremment déjà "exploré" les solutions mises en oeuvres par les scripts SQL des MemberShip Providers pour faire de la pagination de données.
Et comme moi, peut être, vous avez trouvé ça quelque peu ... lourd.

Bon, en même temps, ces scripts se veulent compatibles avec SQL Server 2000 par exemple. De ce fait, les cht'i gars de chez MS n'avaient guère le choix ...

Mais nous, si !

Alors comment faire pour récupérer un jeu de données paginées ?

Tout d'abord, nous allons utiliser Row_Number(), qui a la bonne idée de retourner un numéro de ligne

Par exemple :

Select Row_Number() over (order by BirthDate asc) as RowNumber, E.EmployeeID, E.ManagerID,
E.BirthDate From  HumanResources.Employee E

retourne :

1        268       273         1941-11-17
2        246      51          1942-04-03
3        238       274        1942-06-13
4        9         3            1942-10-29
5        237       108        1943-06-01
6        260      49          1944-05-25
7        243      18          1945-03-02
8       7         21           1946-02-16
9       10        185          1946-04-27
10      19        185          1946-04-30
11      14        21            1946-05-03
12      17        185          1946-05-06

Malheuresement, Row_Number() est analysé par le moteur APRES la clause WHERE.
Du coup interdiction de faire quelque chose du genre :

where Row_Number() over (order by BirthDate asc) > 100 
and Row_Number() over (order by BirthDate asc) < 120

D'où l'idée d'utiliser une Common Type Expression, qui aura la bonne idée de nous fournir notre précieuse information !

Ce qui donne dans notre exemple :

With mCte (RowNumber, EmployeeID, ManagerID, BirthDate)
As
(
 Select Row_Number() over (order by BirthDate asc) as RowNumber, E.EmployeeID, 
 E.ManagerID, E.BirthDate
 From    HumanResources.Employee E
)
Select    *
From mCte C
Where    RowNumber Between 100 and 120

Avec ASP.Net et une data source, il est de bon ton de donner un numéro de page et un nombre d'enregistrements à récupérer.

Si on arrange tout ça pour s'adapter à ce contexte, on obtient une procédure stockée du style :

CREATE procedure [GetEmployeesPaged]
(
    @nStartRowNum int = 100,
    @nStartRowCount int = 10
    
)

as

With mCte (RowNumber, EmployeeID, ManagerID, BirthDate)
As
(
 Select Row_Number() over (order by BirthDate asc) as RowNumber, 
 E.EmployeeID, E.ManagerID, E.BirthDate
 From    HumanResources.Employee E
)
Select    *
From mCte C
Where    RowNumber Between @nStartRowNum and (@nStartRowNum + @nStartRowCount -1)

Pour aller plus loin :

Si on pousse un peu, je vous propose une Double CTE pour récupérer non seulement les employés mais aussi les managers de ceux ci, ce qui va nous obliger à faire une boucle récursive, ce qui est naturel avec une CTE.

bref, plus que des mots ... du SQL !

With    
    EmpCTE (EmpID, MgrID, Level)
    As    (
         Select E.EmployeeID, E.ManagerID, 1 
         From    HumanResources.Employee E
         Where ManagerID is null
         UNION ALL
         Select E.EmployeeID, E.ManagerID, Level + 1 
         From    HumanResources.Employee E
         Inner Join EmpCTE on EmpCTE.EmpID = E.ManagerID
         ),
    myCTE 
    As (    
        Select    Row_Number() over (order by Level asc) as RowNum, *
        From    EmpCTE
        )

Select    C.RowNum, C.EmpID, CE.FirstName+ ' ' + CE.LastName as EmpName,
        C.MgrID, ME.FirstName+ ' ' + ME.LastName as MgrName, C.Level
From myCTE C
Inner Join Person.Contact CE on C.EmpID = CE.ContactID
Left Outer Join Person.Contact ME on C.MgrID = ME.ContactID

Where    RowNum Between @nStartRowNum and (@nStartRowNum + @nStartRowCount -1)

C'est beau non  ;) ?