Dans cet article, nous explorons les clauses OFFSET et FETCH. OFFSET et FETCH sont utilisés conjointement avec la clause SELECT ORDER BY pour fournir un moyen de récupérer une plage d’enregistrements. La ligne de départ à renvoyer est déterminée par la valeur de DÉCALAGE et le nombre maximum de lignes à renvoyer à partir de ce point par EXTRACTION.

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 Pour commencer à utiliser SQL Server.

Utiliser OFFSET et FETCH avec la clause ORDER BY

Renvoyer des lignes à partir d’une instruction SQL peut être une affaire de tout ou rien. Dans de nombreux cas, le nombre de lignes renvoyées est très important, ce qui peut poser des problèmes si vous n’avez besoin que d’une partie du jeu de résultats.

Lorsque les résultats sont triés à l’aide de la clause ORDER BY, certaines options entrent en jeu pour limiter le nombre de lignes renvoyées :

1. Vous pouvez utiliser TOP pour renvoyer un nombre spécifié de lignes.
2. Vous pouvez utiliser OFFSET et FETCH.

Dans cet article, nous nous plongeons dans en savoir plus sur OFFSET et FETCH. Pour en savoir plus sur TOP, lisez l’article Premiers pas avec SQL Server:2. Triez Les Résultats De Votre Requête.

OFFSET

L’argument OFFSET est utilisé pour identifier le point de départ pour renvoyer des lignes à partir d’un résultat. OFFESET est appelé un argument car il fait techniquement partie de la clause ORDER BY. Le DÉCALAGE est le nombre de lignes à ignorer avant de les inclure dans le résultat.

La forme générale de l’argument OFFSET est:

SELECT columnsFROM tableORDER BY columns OFFSET rows-to-skip ROWS

Où les lignes à ignorer sont une valeur supérieure ou égale à zéro.

Par exemple, pour afficher tous les employés sauf les 10 premiers, classés par date d’embauche, vous pouvez écrire

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS

Voici quelques éléments à considérer à propos de OFFSET

• OFFSET fait partie de la clause ORDER BY. Il ne peut pas être utilisé seul.
• Les valeurs de décalage doivent être nulles ou supérieures. Un nombre négatif entraîne une erreur.
• Lorsque le DÉCALAGE est égal à 0, aucune ligne n’est ignorée.
• Si le DÉCALAGE est supérieur au nombre de lignes dans les résultats ordonnés, aucune ligne n’est renvoyée.

FETCH

L’argument FETCH est utilisé pour renvoyer un nombre défini de lignes. FETCH ne peut pas être utilisé seul, il est utilisé conjointement avec OFFSET.

En poursuivant notre exemple, nous pouvons montrer les 11e à 15e employés embauchés en utilisant cette déclaration

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

Dans le diagramme suivant, vous pouvez voir OFFSET et FETCH au travail. OFFSET est utilisé pour ignorer les 10 premières lignes et FETCH est ensuite utilisé pour afficher les 5 suivantes.

La combinaison de OFFSET et FETCH permet de récupérer facilement une fenêtre « coulissante” de lignes. Le début de la fenêtre est déterminé par OFFSET et la hauteur par FETCH.

Utilise pour le DÉCALAGE et la RÉCUPÉRATION

Pagination

L’une des utilisations les plus populaires pour le DÉCALAGE et la RÉCUPÉRATION est la pagination. Vous avez sans doute visité un site Web où vous voyez une liste d’éléments et en bas une liste de numéros de page ou un bouton suivant.

Nous utilisons la pagination tout le temps sur le web. L’exemple le plus populaire auquel je peux penser est Google:

Les chiffres ci-dessous Google représentent les numéros de page. Une partie des résultats de recherche est renvoyée à chaque clic.

De la même manière, supposons que nous ayons une page Web affichant les employés par date d’embauche. Si nous voulions afficher 20 employés sur une page, et que nous affichions la 3ème page (employés 21-30), nous pourrions utiliser la requête suivante:

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Cette requête demande à SQL de :

1. Sélectionnez les informations sur l’employé
2. Commandez les informations par date d’embauche
3. Sautez 20 lignes et commencez à afficher les résultats à partir de la 21e
4. Affichez les 10 lignes suivantes de résultats.

Obtenir les enregistrements supérieurs

Si vous souhaitez obtenir les dix premières lignes d’une requête, vous pouvez le faire en définissant OFFSET sur 0. N’oubliez pas que le DÉCALAGE spécifie le nombre de lignes à ignorer. En le mettant à zéro, nous disons à SQL de commencer à la première ligne.

Une fois que OFFSET est défini, l’ordre de travail suivant consiste à définir FETCH. Puisque nous recherchons les dix premiers, nous mettons FETCH À CÔTÉ de 10.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Si vous connaissez la clause TOP, vous avez peut-être remarqué qu’il existe des similitudes entre ces deux méthodes. Par exemple, l’exemple ci-dessus utilisant TOP ressemblerait à

SELECT TOP 10 NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate

Il y a des aspects à TOP qui ne s’appliquent pas à OFFSET et à FETCH; comme être autorisé dans des instructions sans ORDER BY, mais comme vous pouvez le voir pour cet exemple, ils renvoient des résultats équivalents.

Obtenir les enregistrements inférieurs

Pour obtenir les enregistrements inférieurs dans un jeu de résultats, il existe deux façons. La première consiste à ordonner le résultat par ordre décroissant plutôt que croissant. C’est place les résultats du bas vers le haut. Ensuite, vous pouvez utiliser fetch comme d’habitude.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Si vous ne souhaitez pas modifier l’ordre des résultats, vous pouvez utiliser une sous-requête pour obtenir le nombre d’enregistrements. Cela peut ensuite être utilisé pour régler le décalage en conséquence.

SELECT NationalIDNumber, JobTitle, HireDateFROM HumanResources.EmployeeORDER BY HireDate OFFSET (SELECT COUNT(*) FROM HumanResources.Employee)-10 ROWS FETCH NEXT 10 ROWS ONLY

Il y a un certain danger dans cette méthode car l’expression pour calculer le DÉCALAGE peut entraîner une valeur inférieure à zéro. Dans notre exemple, cela pourrait se produire si le nombre total de lignes était inférieur à dix. Pour vous défendre contre cette condition, voudriez-vous incorporer une déclaration de CAS dans votre logique pour vérifier par rapport à cette condition.

Données d’échantillonnage au milieu d’un jeu de résultats

Une fonctionnalité intéressante de FETCH and OFFSET vous permet d’écrire SQL pour échantillonner ou tester des données à partir du milieu du résultat. C’est très pratique si vous avez besoin de voir jeter un coup d’œil au milieu d’un jeu de résultats, peut-être celui contenant des millions de lignes, sans afficher toutes les lignes jusqu’au point que vous souhaitez revoir.

Pour échantillonner au milieu, vous utiliseriez la même logique que pour la pagination. Bien sûr, le nombre de lignes que vous récupérez dans ce cas peut être beaucoup plus grand.

Effet de ORDER BY sur OFFSET et FETCH

OFFSET et FETCH ne fonctionnent qu’en conjonction avec une clause ORDER BY. En effet, SQL récupère d’abord les données que vous avez spécifiées, telles que les colonnes, puis ordonne les données par ordre croissant ou décroissant.

Ce n’est qu’une fois cette étape terminée que les lignes sont ignorées et que les résultats sont produits.

Cas limites

Étant donné que certaines valeurs peuvent entraîner une erreur, comme un DÉCALAGE négatif, explorons diverses combinaisons de valeurs pouvant dépasser le nombre de lignes d’une table pour comprendre quelles valeurs sont sûres à utiliser et quelles valeurs déclencheraient une erreur SQL.

Nous baserons nos exemples sur les ressources humaines.Table des employés, qui contient 290 lignes.

Le seul cas qui entraîne une erreur est lorsque le DÉCALAGE est négatif. Il y a des cas qui ne renvoient pas de lignes ou qui peuvent renvoyer moins de lignes que vous ne le pensez, mais ces cas ne génèrent pas d’erreurs. Pour la plupart, ces situations se produisent lorsque la valeur de DÉCALAGE est supérieure au nombre de lignes dans le résultat (toutes les lignes sont ignorées), ou que vous essayez de récupérer « passé” puis la fin du résultat.