Quelle est la meilleure façon de paginer les résultats dans SQL Server

Question

Ce qui est le meilleur moyen (performance sage) pour paginer les résultats dans SQL Server 2000, 2005, 2008, 2012 si vous voulez également obtenir le nombre total de résultats (avant pagination) ?

Answer 1

J'ai été également très curieux au sujet de la raison pour laquelle Microsoft ne prend pas en charge les requêtes simples avec offset/limite comme MySQL ou PostgreSQL. Enfin, sorti de Microsoft SQL Server 2012, j'aime beaucoup sa simplicité pour la pagination, vous n'avez pas à utiliser des requêtes complexes comme la réponse ici.

Pour obtenir les 10 prochaines lignes, juste exécuter cette requête:

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

http://technet.microsoft.com/en-us/library/gg699618.aspx

Answer 2

Obtenir le nombre total des résultats et la pagination sont deux opérations différentes. Pour les besoins de cet exemple, supposons que la requête que vous avez affaire avec est

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

Dans ce cas, vous devez déterminer le nombre total de résultats à l'aide de:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

...ce qui peut sembler inefficace, mais est en fait assez performant, en supposant que tous les indices etc. sont correctement configurés.

Ensuite, pour obtenir des résultats réels de retour dans un paginé de la mode, la requête suivante serait le plus efficace:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Cela permettra de retourner les lignes 1 à 19 de la requête d'origine. Ce qui est cool ici, en particulier pour les applications web, c'est que vous n'avez pas à garder un état, sauf les numéros de ligne pour être retourné.

Answer 3

Incroyablement, pas d'autre réponse a mentionné la plus rapide façon de faire de la pagination dans toutes les versions de SQL Server. Les décalages peuvent être terriblement lent pour les grands numéros de page est comparé ici. Il est tout à fait différent, beaucoup plus rapide pour effectuer l'échange en SQL. Ceci est souvent appelé la "méthode de recherche" ou "jeu de clés d'échange" comme décrit dans ce billet de blog ici.

SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

Le "chercher le prédicat"

L' @previousScore et @previousPlayerId des valeurs sont les valeurs respectives du dernier enregistrement de la page précédente. Cela vous permet de chercher les "à côté" de la page. Si l' ORDER BY direction ASC, il suffit d'utiliser > à la place.

Avec la méthode ci-dessus, vous ne pouvez pas sauter immédiatement à la page 4, sans avoir d'abord cherché le précédent de 40 dossiers. Mais souvent, vous ne voulez pas sauter aussi loin de toute façon. Au lieu de cela, vous obtenez un beaucoup plus rapide requête qui pourrait être en mesure d'extraire des données en temps constant, en fonction de votre indexation. De Plus, vos pages de rester "stable", peu importe si les données sous-jacentes des changements (p. ex., page 1, alors que vous êtes sur la page 4).

C'est la meilleure façon de mettre en œuvre la pagination lorsque le chargement paresseux plus de données dans les applications web, par exemple.

Remarque, la "méthode de recherche" est aussi appelé jeu de clés de pagination.

Nombre Total d'enregistrements avant de pagination

L' COUNT(*) OVER() fenêtre vous permettra de compter le nombre total d'enregistrements "avant d'échange". Si vous utilisez SQL Server 2000, vous devrez recourir à deux requêtes pour l' COUNT(*).

Answer 4

  RowNumber FirstName    LastName               SalesYTD
  --- -----------  ---------------------- -----------------
  1   Linda        Mitchell               4251368.54
  2   Jae          Pak                    4116871.22
  3   Michael      Blythe                 3763178.17
  4   Jillian      Carson                 3189418.36
  5   Ranjit       Varkey Chudukatil      3121616.32
  6   José         Saraiva                2604540.71
  7   Shu          Ito                    2458535.61
  8   Tsvi         Reiter                 2315185.61
  9   Rachel       Valdez                 1827066.71
  10  Tete         Mensa-Annan            1576562.19
  11  David        Campbell               1573012.93
  12  Garrett      Vargas                 1453719.46
  13  Lynn         Tsoflias               1421810.92
  14  Pamela       Ansman-Wolfe           1352577.13

    WITH OrderedOrders AS
    (
        ROW_NUMBER() OVER(ORDER BY FirstName DESC) AS RowNumber, 
        FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
        FROM [dbo].[vSalesPerson]
    ) 
    SELECT RowNumber, 
        FirstName, LastName, Sales YTD 
    FROM OrderedOrders 
    WHERE RowNumber > 50 AND RowNumber < 60;

Answer 5

Il y a un bon aperçu des différentes techniques de pagination au http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

J’ai utilisé la méthode ROWCOUNT assez souvent surtout avec SQL Server 2000 (fonctionne avec 2 k 5 et 2 k 8 performance mesure aussi, juste par rapport à ROW_NUMBER), il est rapide comme l’éclair, mais vous devez vous assurer que l’ou les colonnes triée ont des valeurs uniques (pour la plupart).