ORDER BY + OFFSET | SQLServer.fr

Pour marquer l’arrivée de la CTP de SQL Server 2011 (nom de code Denali), j’ai choisi d’écrire un petit mot sur une des nouveautés que cette version propose au niveau du codage T-SQL. Il s’agit d’un complément de syntaxe pour la clause ORDER BY, qui permet par exemple la gestion directe d’une pagination dans le rendu des résultats.
Afin de mieux comprendre l’utilité de cette syntaxe, j’ai choisi d’exprimer un besoin et de montrer comment ce besoin pourrait être développé avec une syntaxe compatible avec SQL Server 2000, SQL Server 2005 et enfin avec la nouvelle syntaxe SQL Server 2011.
Le test de syntaxe s’appuiera sur la base de démonstration AdventureWorks2008R2, que l’on peut trouver ici : http://msftdbprodsamples.codeplex.com/. Il consiste à obtenir retourner un listing des ventes avec Numéro de commande, Date de commande et Acheteur. Jusqu’ici, rien de bien spécial, mais rajoutons surtout que ce listing doit s’afficher de manière paginée à l’écran, et que nous cherchons en fait à afficher les @NbElements premiers éléments à par de l’élément numéro @NoPremierElement, en triant suivant la date et le numéro de commande.

Côté SQL Server 2000, étant donné différentes contraintes liées à la syntaxe (par exemple le fait que la clause TOP ne pouvait pas recevoir de variable), on obtient un code assez lourd qui passe par une jolie petite boucle.

CREATE PROCEDURE [dbo].[ps_Page2000]
@NoPremierElement int,
@NbElements int
AS
BEGIN

	DECLARE @SalesOrderID int
	DECLARE @Compteur int
	create table #Liste (SalesOrderID int, PurchaseOrderNumber nvarchar(25), OrderDate datetime, SalesPersonID int, Selection bit)

	INSERT INTO #Liste (SalesOrderID,PurchaseOrderNumber, OrderDate, SalesPersonID)
	SELECT soh.SalesOrderID,
			soh.PurchaseOrderNumber,
			soh.OrderDate,
			soh.SalesPersonID
	FROM Sales.SalesOrderHeader soh
	WHERE SalesPersonID is not null

	SELECT @Compteur=0

	DECLARE Curseur CURSOR FOR
	SELECT l.SalesOrderID
	FROM #Liste l
	ORDER BY l.OrderDate,l.PurchaseOrderNumber
	FOR UPDATE OF l.Selection

    OPEN Curseur
    FETCH NEXT FROM Curseur INTO @SalesOrderID

    WHILE @@FETCH_STATUS = 0
    BEGIN

		SELECT @Compteur=@Compteur+1

		IF @Compteur>=@NoPremierElement+@NbElements
			BREAK;

		IF @Compteur>=@NoPremierElement
			UPDATE #Liste SET Selection=1 where SalesOrderID=@SalesOrderID

		FETCH NEXT FROM Curseur INTO @SalesOrderID
    END

    CLOSE Curseur
    DEALLOCATE Curseur

	SELECT l.PurchaseOrderNumber,
			l.OrderDate,
			p.LastName + ' ' + p.FirstName
	FROM #Liste l
	JOIN Person.Person p on p.BusinessEntityID=l.SalesPersonID
	WHERE l.Selection=1
	ORDER BY l.OrderDate,l.PurchaseOrderNumber

END

Certes, le code n’est pas spécialement optimisé, mais l’idée principale y est, et il fonctionne.

SQL Server 2005 nous a apporté pas mal de nouvelles fonctionnalités, dont notamment les fonctions de classement qui sont ici très utiles. A noter qu’il y a là encore d’autres syntaxes possibles, celle que je propose ici a juste le mérite de fonctionner…

CREATE PROCEDURE [dbo].[ps_Page2005]
@NoPremierElement int,
@NbElements int
AS
BEGIN

	WITH Liste (SalesOrderID,PurchaseOrderNumber, OrderDate, SalesPersonID, Ordre) as
		(SELECT soh.SalesOrderID,
				soh.PurchaseOrderNumber,
				soh.OrderDate,
				soh.SalesPersonID,
				ROW_NUMBER() OVER (ORDER BY soh.OrderDate,soh.PurchaseOrderNumber)
		FROM Sales.SalesOrderHeader soh
		WHERE SalesPersonID is not null)
	SELECT l.PurchaseOrderNumber,
			l.OrderDate,
			p.LastName + ' ' + p.FirstName
	FROM Liste l
	JOIN Person.Person p on p.BusinessEntityID=l.SalesPersonID
	WHERE l.Ordre>=@NoPremierElement
	and l.Ordre<@NoPremierElement+@NbElements
	ORDER BY l.Ordre;

END

Et en plus, c’est déjà en une seule instruction, certes complexe, mais c’est déjà bien plus lisible que le curseur de la version 2000 !

Enfin, nous arrivons à la fameuse nouvelle syntaxe de Denali, objet de cet article.

CREATE PROCEDURE [dbo].[ps_Page2011]
@NoPremierElement int,
@NbElements int
AS
BEGIN

	SELECT soh.SalesOrderID,
			soh.PurchaseOrderNumber,
			soh.OrderDate,
			soh.SalesPersonID
	FROM Sales.SalesOrderHeader soh
	JOIN Person.Person p on p.BusinessEntityID=soh.SalesPersonID
	WHERE soh.SalesPersonID is not null
	ORDER BY soh.OrderDate,soh.PurchaseOrderNumber OFFSET @NoPremierElement ROWS FETCH NEXT @NbElements ROWS ONLY;

END

Voilà, plus besoin d’y aller en plusieurs étapes (ordonnancement + filtre), tout est fait d’un coup.
A noter toutefois que, même si l’on obtient une syntaxe beaucoup plus réduite et maintenable, il est toujours nécessaire d’avoir une pensée pour les performances, étant donné que la clause ORDER BY est exécutée en dernier lieu, et donc le filtre aussi.

Si l’on cherche donc à avoir un petit extrait résultat de jointures complexes, il peut parfois être intéressant d’éviter les syntaxes à une seule instruction. Mais ça, c’est une autre histoire…

Laisser un commentaire Annuler la réponse