TOP et OFFSET & FETCH peuvent être utilisés pour limiter le nombre de lignes renvoyées. OFFSET et FETCH peuvent renvoyer des résultats similaires en haut, mais il existe des différences qui peuvent influencer la meilleure méthode à utiliser dans votre situation donnée.
Tous les exemples de cette leçon sont basés sur Microsoft SQL Server Management Studio et la base de données AdventureWorks2012. Vous pouvez commencer à utiliser ces outils gratuits en utilisant mon Guide Premiers pas avec SQL Server.
Quelle est la différence entre TOP et OFFSET & Fetch?
Dans les sections suivantes, nous couvrons TOP et OFFSET & FETCH. Je pense quune fois que vous aurez lu les deux sections, vous comprendrez leurs différences. Celles-ci sont également résumées dans un tableau à la fin de cet article.
Haut
TOP est utilisé pour renvoyer un nombre spécifique de lignes à partir dun résultat de requête.
La clause TOP est disponible depuis un certain temps dans les anciennes versions de SQL Server, telles que SQL 2005. TOP nest pas conforme à ANSI et son utilisation est essentiellement limitée aux produits Microsoft tels que SQL Server et MS-Access.
La clause TOP est utilisée dans la liste des colonnes de linstruction SELECT et peut être utilisée avec ou sans ORDER BY.
Par exemple, pour sélectionner les dix produits les moins coûteux, utilisez
SELECT TOP 10 Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost
Bien que ORDER BY ne soit pas obligatoire, il est recommandé, car sans lui, vous navez aucune garantie pour les lignes renvoyées.
Un élément unique à TOP est la possibilité de renvoyer un pourcentage de lignes. Linstruction
SELECT TOP 10 PERCENT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost
renvoie les 51 premiers produits avec le coût standard le plus bas (cest-à-dire 504 lignes x 10% = 50,4, arrondi à 51).
Vous pouvez également sélectionner le nombre spécifié denregistrements à partir du bas du résultat dune requête. Pour ce faire, vous pouvez utiliser une astuce pour classer les enregistrements par ordre décroissant:
SELECT TOP 10 Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost DESC
Gardez à lesprit que lorsque les résultats sont triés par ordre décroissant, le les montants les plus importants sont retournés en premier. Pour cette raison, TOP renvoie les produits dont le coût standard est le plus élevé.
TOP peut être très utile sur des résultats volumineux car le renvoi de milliers de lignes peut affecter les performances. Dans de nombreux cas, les utilisations ne feront que parcourir la partie de début des résultats.
TOP peut afficher la valeur des liens – WITH TIES vous permet également dafficher des enregistrements supplémentaires qui ont la même valeur que la dernière ligne du résultat de base.
OFFSET et FETCH
OFFSET et FETCH sont utilisés pour renvoyer une fenêtre denregistrements à partir dun jeu de résultats. OFFSET spécifie le nombre de lignes à ignorer dans le résultat et FETCH spécifie le nombre de lignes à partir de ce point à renvoyer dans le résultat.
OFFSET et FETCH ont été récemment introduits dans SQL Server 2012 et sont conformes à la norme ANSI.
Vous pouvez utiliser OFFSET sans FETCH, mais FETCH ne peut pas être utilisé seul. Quoi quil en soit, OFFSET doit être utilisé avec une clause ORDER BY. La raison est simple car OFFSET et FETCH font partie de la clause ORDER BY.
Dans cet exemple, les dix premières lignes du résultat sont ignorées, puis les 10 suivantes affichées dans le résultat.
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
Vous pouvez répliquer les lignes renvoyées par TOP en utilisant OFFSET et FETCH à condition que les résultats soient classés. Linstruction suivante équivaut à renvoyer le TOP 10:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Lorsque OFFSET est défini sur 0, aucune ligne nest ignorée.
Contrairement TOP, avec OFFSET, il ny a pas de moyen intégré pour renvoyer un pourcentage de lignes. Vous pouvez cependant rouler le vôtre en calculant le pourcentage comme suit:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost OFFSET 0 ROWS FETCH NEXT (SELECT CAST(CEILING(COUNT(*) * .1) as INT) FROM Production.Product) ROWS ONLY
Lastuce se trouve dans la sous-requête. En calculant le nombre de lignes dans le résultat, nous pouvons ensuite obtenir un pourcentage. Voici la sous-requête. Si vous le souhaitez, vous pouvez lexécuter dans SQL Server Management Studio.
SELECT CAST(CEILING(COUNT(*) * .1) as INT)FROM Production.Product
Il y a plusieurs éléments à noter:
- CEILING arrondit les nombres vers le haut. Dans notre exemple CEILING (50.4) renvoie 51.0. Cela imite le comportement de TOP.
- FETCH nécessite une valeur entière, donc CAST est utilisé pour effectuer cette conversion. Ainsi 51.0 devient, 51
Pour renvoyer les 10 derniers enregistrements dun résultat, il existe deux méthodes. Nous pouvons inverser lordre de tri comme nous lavons fait pour TOP:
SELECT Name, ProductNumber, StandardCostFROM Production.ProductORDER BY StandardCost DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Ou nous pouvons conserver lordre de tri et calculer le décalage de telle sorte que toutes les lignes jusquà la dernière dix sont ignorés. Nous explorons cette technique plus en détail dans la leçon SQL sur OFFSET et FETCH.
Résumé des différences
Voici un résumé de quelques différences majeures entre TOP et OFFSET & FETCH.
En général, si votre objectif est de simplement renvoyer les premières lignes, alors je pense que TOP est une solution simple, cependant; si vous recherchez une solution de pagination ou si vous avez besoin de sélectionner des lignes au milieu ou au bas des résultats de la requête sans affecter lordre de tri dorigine, alors je pense que OFFSET & FETCH sont les meilleurs.