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…