Les filtres classiques en SQL comme ON, WHERE et HAVING sont basés sur des prédicats. TOP et OFFSET-FETCH sont des filtres qui sont basés sur un concept différent : vous indiquez l’ordre et le nombre de lignes à filtrer en fonction de cet ordre. De nombreuses tâches de filtrage sont définies sur la base de l’ordre et d’un nombre requis de lignes. Il est certainement bon d’avoir un support linguistique dans T-SQL qui vous permet de formuler la requête d’une manière qui est similaire à la façon dont vous pensez à la tâche.
Ce chapitre commence par les aspects de conception logique des filtres. Il utilise ensuite un scénario de pagination pour démontrer leur optimisation. Le chapitre couvre également l’utilisation de TOP avec des déclarations de modification. Enfin, le chapitre démontre l’utilisation de TOP et OFFSET-FETCH dans la résolution de problèmes pratiques comme le top N par groupe et la médiane.
Les filtres TOP et OFFSET-FETCH
Vous utilisez les filtres TOP et OFFSET-FETCH pour mettre en œuvre les exigences de filtrage dans vos requêtes de manière intuitive. Le filtre TOP est une fonctionnalité propriétaire de T-SQL, tandis que le filtre OFFSET-FETCH est une fonctionnalité standard. T-SQL a commencé à prendre en charge OFFSET-FETCH avec Microsoft SQL Server 2012. À partir de SQL Server 2014, l’implémentation de OFFSET-FETCH dans T-SQL manque encore quelques éléments standard – de manière intéressante, ceux qui sont disponibles avec TOP. Avec l’implémentation actuelle, chacun des filtres a des capacités qui ne sont pas prises en charge par l’autre.
Je vais commencer par décrire les aspects de conception logique de TOP et ensuite couvrir ceux de OFFSET-FETCH.
Le filtre TOP
Le filtre TOP est une construction couramment utilisée dans T-SQL. Sa popularité peut probablement être attribuée au fait que sa conception est si bien alignée avec la façon dont de nombreuses exigences de filtrage sont exprimées – par exemple, « Retourner les trois commandes les plus récentes. » Dans cette requête, l’ordre du filtre est basé sur orderdate, descendant, et le nombre de lignes que vous voulez filtrer en fonction de cet ordre est 3.
Vous spécifiez l’option TOP dans la liste SELECT avec une valeur d’entrée tapée comme BIGINT indiquant le nombre de lignes que vous voulez filtrer. Vous fournissez la spécification de l’ordre dans la clause ORDER BY classique. Par exemple, vous utilisez la requête suivante pour obtenir les trois commandes les plus récentes.
USE TSQLV3;SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
J’ai obtenu le résultat suivant de cette requête:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
Au lieu de spécifier le nombre de lignes que vous voulez filtrer, vous pouvez utiliser TOP pour spécifier le pourcentage (du nombre total de lignes dans le résultat de la requête). Pour ce faire, vous devez fournir une valeur comprise entre 0 et 100 (saisie en tant que FLOAT) et ajouter le mot-clé PERCENT. Par exemple, dans la requête suivante, vous demandez à filtrer un pour cent des lignes :
SELECT TOP (1) PERCENT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
SQL Server arrondit au chiffre supérieur le nombre de lignes calculé en fonction du pourcentage saisi. Par exemple, le résultat de 1 pour cent appliqué à 830 lignes de la table Commandes est 8,3. En arrondissant ce nombre, vous obtenez 9. Voici le résultat que j’ai obtenu pour cette requête :
orderid orderdate custid empid----------- ---------- ----------- -----------11074 2015-05-06 73 711075 2015-05-06 68 811076 2015-05-06 9 411077 2015-05-06 65 111070 2015-05-05 44 211071 2015-05-05 46 111072 2015-05-05 20 411073 2015-05-05 58 211067 2015-05-04 17 1
Notez que pour traduire le pourcentage d’entrée en un nombre de lignes, SQL Server doit d’abord calculer le nombre de lignes dans le résultat de la requête, ce qui nécessite généralement un travail supplémentaire.
Intéressant, la spécification de l’ordre est facultative pour le filtre TOP. Par exemple, considérez la requête suivante:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.Orders;
J’ai obtenu la sortie suivante de cette requête:
orderid orderdate custid empid----------- ---------- ----------- -----------10248 2013-07-04 85 510249 2013-07-05 79 610250 2013-07-08 34 4
La sélection des trois lignes à retourner est non déterministe. Cela signifie que si vous exécutez à nouveau la requête, sans que les données sous-jacentes ne changent, vous pourriez théoriquement obtenir un ensemble différent de trois lignes. En pratique, la sélection des lignes dépend de conditions physiques telles que les choix d’optimisation, les choix de moteur de stockage, la disposition des données et d’autres facteurs. Si vous exécutez la requête plusieurs fois, tant que ces conditions physiques ne changent pas, il y a de fortes chances que vous obteniez les mêmes résultats. Mais il est essentiel de comprendre le principe d' »indépendance physique des données » du modèle relationnel, et de se rappeler qu’au niveau logique, vous n’avez aucune garantie de résultats répétables. Sans spécification d’ordre, vous devez considérer que l’ordre est arbitraire, ce qui entraîne une sélection de lignes non déterministe.
Même lorsque vous fournissez une spécification d’ordre, cela ne signifie pas que la requête est déterministe. Par exemple, une requête TOP antérieure a utilisé orderdate, DESC comme spécification d’ordre. La colonne orderdate n’est pas unique ; par conséquent, la sélection entre les lignes ayant la même date de commande n’est pas déterministe. Que faites-vous donc dans les cas où vous devez garantir le déterminisme ? Il existe deux options : l’utilisation de l’option WITH TIES ou de l’ordre unique.
L’option WITH TIES entraîne l’inclusion des liens dans le résultat. Voici comment l’appliquer à notre exemple :
SELECT TOP (3) WITH TIES orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC;
Voici le résultat que j’ai obtenu avec cette requête :
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 811074 2015-05-06 73 7
Le serveur SQL filtre les trois lignes avec les dates de commande les plus récentes, plus il inclut toutes les autres lignes qui ont la même date de commande que dans la dernière ligne. Par conséquent, vous pouvez obtenir plus de lignes que le nombre que vous avez spécifié. Dans cette requête, vous avez spécifié que vous vouliez filtrer trois lignes, mais vous en avez obtenu quatre. Ce qui est intéressant à noter ici, c’est que la sélection des lignes est maintenant déterministe, mais que l’ordre de présentation entre les lignes ayant la même date de commande n’est pas déterministe.
La deuxième méthode pour garantir un résultat déterministe consiste à rendre la spécification de commande unique en ajoutant un bris d’égalité. Par exemple, vous pourriez ajouter orderid, DESC comme bris d’égalité dans notre exemple. Cela signifie qu’en cas d’égalité dans les valeurs de date de commande, une ligne avec une valeur d’ID de commande supérieure est préférée à une ligne avec une valeur inférieure. Voici notre requête avec le bris d’égalité appliqué:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESC;
Cette requête génère la sortie suivante:
orderid orderdate custid empid----------- ---------- ----------- -----------11077 2015-05-06 65 111076 2015-05-06 9 411075 2015-05-06 68 8
L’utilisation de l’ordre unique rend déterministe la sélection des lignes et l’ordre de présentation. Le jeu de résultats ainsi que l’ordre de présentation des lignes sont garantis d’être répétables tant que les données sous-jacentes ne changent pas.
Si vous avez un cas où vous devez filtrer un certain nombre de lignes mais que vous ne vous souciez vraiment pas de l’ordre, cela pourrait être une bonne idée de spécifier ORDER BY (SELECT NULL), comme ceci:
SELECT TOP (3) orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL);
De cette façon, vous faites savoir à tout le monde que votre choix d’ordre arbitraire est intentionnel, ce qui permet d’éviter la confusion et le doute.
Pour rappeler ce que j’ai expliqué au chapitre 1, « Traitement logique des requêtes », à propos des filtres TOP et OFFSET-FETCH, l’ordre de présentation n’est garanti que si la requête externe comporte une clause ORDER BY. Par exemple, dans la présentation de la requête suivante, l’ordre n’est pas garanti:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS D;
Pour fournir une garantie d’ordre de présentation, vous devez spécifier une clause ORDER BY dans la requête externe, comme suit:
SELECT orderid, orderdate, custid, empidFROM ( SELECT TOP (3) orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC ) AS DORDER BY orderdate DESC, orderid DESC;
Le filtre OFFSET-FETCH
Le filtre OFFSET-FETCH est une fonctionnalité standard conçue de manière similaire à TOP mais avec un élément supplémentaire. Vous pouvez spécifier le nombre de lignes que vous voulez sauter avant de spécifier le nombre de lignes que vous voulez filtrer.
Comme vous avez pu le deviner, cette fonctionnalité peut être pratique pour mettre en œuvre des solutions de pagination – c’est-à-dire renvoyer un résultat à l’utilisateur un morceau à la fois sur demande lorsque l’ensemble complet de résultats est trop long pour tenir dans un écran ou une page Web.
Le filtre OFFSET-FETCH nécessite l’existence d’une clause ORDER BY, et il est spécifié juste après celle-ci. Vous commencez par indiquer le nombre de lignes à ignorer dans une clause OFFSET, puis le nombre de lignes à filtrer dans une clause FETCH. Par exemple, sur la base de l’ordre indiqué, la requête suivante saute les 50 premières lignes et filtre les 25 lignes suivantes :
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;
En d’autres termes, la requête filtre les lignes 51 à 75. En termes de pagination, en supposant une taille de page de 25 lignes, cette requête renvoie la troisième page.
Pour permettre un langage déclaratif naturel, vous pouvez utiliser le mot clé FIRST au lieu de NEXT si vous le souhaitez, bien que la signification soit la même. L’utilisation de FIRST pourrait être plus intuitive si vous ne sautez aucune ligne. Même si vous ne voulez pas sauter de lignes, T-SQL rend toujours obligatoire la clause OFFSET (avec 0 ROWS) pour éviter toute ambiguïté d’analyse syntaxique. De même, au lieu d’utiliser la forme plurielle du mot-clé ROWS, vous pouvez utiliser la forme singulière ROW dans les clauses OFFSET et FETCH. C’est plus naturel si vous devez sauter ou filtrer une seule ligne.
Si vous êtes curieux de savoir à quoi sert le mot-clé ONLY, il signifie ne pas inclure les liens. Le SQL standard définit l’alternative WITH TIES ; cependant, T-SQL ne la prend pas encore en charge. De même, le langage SQL standard définit l’option PERCENT, mais T-SQL ne la prend pas encore en charge non plus. Ces deux options manquantes sont disponibles avec le filtre TOP.
Comme mentionné, le filtre OFFSET-FETCH nécessite une clause ORDER BY. Si vous voulez utiliser un ordre arbitraire, comme TOP sans clause ORDER BY, vous pouvez utiliser l’astuce avec ORDER BY (SELECT NULL), comme suit :
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY (SELECT NULL)OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
La clause FETCH est facultative. Si vous voulez sauter un certain nombre de lignes mais ne pas limiter le nombre de lignes à retourner, il suffit de ne pas indiquer de clause FETCH. Par exemple, la requête suivante saute 50 lignes mais ne limite pas le nombre de lignes retournées:
SELECT orderid, orderdate, custid, empidFROM Sales.OrdersORDER BY orderdate DESC, orderid DESCOFFSET 50 ROWS;
Concernant l’ordre de présentation, le comportement est le même qu’avec le filtre TOP ; à savoir, avec OFFSET-FETCH également, l’ordre de présentation est garanti seulement si la requête la plus extérieure a une clause ORDER BY.