Par : Ashish Kumar Mehta | Mis à jour : 2011-04-18 | Commentaires (4) | Associé : 1 | 2 | 3 | 4 | 5 | Plus >Paging
Problème
En parcourant les nouvelles fonctionnalités de SQL Server 2012, je suis tombé sur une nouvelle fonctionnalité OFFSET et FETCH. La clause OFFSET and FETCH de SQL Server 2012 vous offre une option pour récupérer seulement une page ou une fenêtre des résultats à partir du jeu de résultats complet. Dans cette astuce, nous allons examiner un exemple qui utilise la fonction OFFSET and FETCH de SQL Server 2012. En outre, nous montrerons comment vous pouvez mettre en œuvre la pagination du serveur SQL ou la récupération des pages de données SQL en utilisant cette nouvelle fonctionnalité.
Solution
En utilisant cette fonctionnalité de SQL Server 2012, on peut facilement mettre en œuvre la pagination du serveur SQL tout en affichant les résultats au client. Nous allons voir un exemple simple et ensuite aussi comment vous pourriez construire une procédure stockée pour mettre en œuvre la pagination SQL.
Voyons un exemple simple qui démontre comment utiliser la fonctionnalité OFFSET et FETCH de SQL Server 2012. Vous pouvez voir ci-dessous que le TSQL ressemble à ce que vous écrivez aujourd’hui, sauf qu’après la clause ORDER BY, nous avons les commandes OFFSET et FETCH. Une chose à noter est que vous devez utiliser un ORDER BY pour utiliser cette fonctionnalité. L’OFFSET indique essentiellement à SQL de sauter les 100 premières lignes et le FETCH obtiendra les 5 lignes suivantes.
USE AdventureWorks2008R2GOSELECT BusinessEntityID ,PersonType ,FirstName + ' ' + MiddleName + ' ' + LastName FROM Person.Person ORDER BY BusinessEntityID ASC OFFSET 100 ROWS FETCH NEXT 5 ROWS ONLYGO
L’extrait ci-dessous montre la sortie lors de l’exécution des commandes ci-dessus. Cela montre que les 100 premières lignes ont été écartées et que la requête a récupéré les 5 lignes suivantes dans le jeu d’enregistrements complet.
Passons par un autre exemple où nous allons créer une procédure stockée qui utilisera la fonctionnalité OFFSET et FETCH de SQL Server 2012 pour réaliser une pagination sql tout en affichant les résultats aux machines clientes. Dans cette procédure stockée, nous transmettons un numéro de page et le nombre de lignes à retourner. Ces valeurs sont ensuite calculées pour obtenir la page et le nombre de lignes correctes.
USE AdventureWorks2008R2GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'.') AND type in (N'P', N'PC'))DROP PROCEDURE .GOCREATE PROCEDURE ExampleUsageOfSQLServerDenaliPagingFeature ( @PageNo INT, @RowCountPerPage INT )ASSELECT BusinessEntityID ,PersonType ,FirstName + ' ' + MiddleName + ' ' + LastName FROM Person.Person ORDER BY BusinessEntityID OFFSET (@PageNo - 1) * @RowCountPerPage ROWS FETCH NEXT @RowCountPerPage ROWS ONLYGO
Allons-y et exécutons la procédure stockée en utilisant la commande ci-dessous. Cela nous donnera cinq enregistrements à partir de la page 21 où les enregistrements sont classés par BusinessEntityID.
/* Display Records Between 101 AND 105 BusinessEntityID */EXECUTE ExampleUsageOfSQLServerDenaliPagingFeature 21, 05GO
Le snippet ci-dessous montre la sortie une fois que la procédure stockée ci-dessus est exécutée avec succès. Vous pouvez voir que les 100 premières (20 pages * 5 lignes par page = 100) lignes ont été écartées et que la procédure stockée a récupéré uniquement les 5 lignes suivantes limitant ainsi le nombre de lignes envoyées au client.
Vous avez vu dans cette astuce comment vous pouvez facilement réaliser la pagination SQL Server en utilisant la fonctionnalité OFFSET et FETCH de SQL Server 2012. La pagination SQL n’est plus aussi difficile qu’avant grâce à cette nouvelle fonctionnalité.
Prochaines étapes
- Ayez un aperçu de la fonctionnalité WITH RESULT SETS de SQL Server 2012
Dernière mise à jour : 2011-04-18
A propos de l’auteur
Ashish Kumar Mehta contribue à la communauté MSSQLTips.com depuis 2009 avec plus de 60 conseils.Voir tous mes conseils
.