What is the best way to paginate results in SQL Server

Question

What is the best way  performance wise  to paginate results in SQL Server 2000  2005  2008  2012 if you also want to get the total number of results  before paginating

User · Accepted Answer

Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is

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

In this case, you would determine the total number of results using:

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

...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.

Next, to get actual results back in a paged fashion, the following query would be most efficient:

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

This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.

User · Answer

From SQL Server 2012  we can use OFFSET and FETCH NEXT Clause to achieve the pagination    Try this  for SQL Server      In the SQL Server 2012 a new feature was added in the ORDER BY clause    to query optimization of a set data  making work easier with data   paging for anyone who writes in T-SQL as well for the entire Execution   Plan in SQL Server       Below the T-SQL script with the same logic used in the previous   example   --CREATING A PAGING WITH OFFSET and FETCH clauses IN  SQL SERVER 2012  DECLARE  PageNumber AS INT   RowspPage AS INT SET  PageNumber   2 SET  RowspPage   10  SELECT ID EXAMPLE  NM EXAMPLE  DT CREATE FROM TB EXAMPLE ORDER BY ID EXAMPLE OFFSET    PageNumber - 1     RowspPage  ROWS FETCH NEXT  RowspPage ROWS ONLY     TechNet  Paging a Query with SQL Server

User · Answer

create PROCEDURE SP Company List   pagesize int   -1   pageindex int  0      gt  AS BEGIN  SET NOCOUNT ON        select  Id   NameEn     from Company  ORDER by Id ASC   OFFSET   pageindex-1     pagesize   ROWS FETCH NEXt  pagesize ROWS ONLY END  GO         DECLARE    return value int  EXEC   return value    dbo   SP Company List           pagesize   1            gt   pageindex   2  SELECT     Return Value     return value  GO

User · Answer

For SQL Server 2000 you can simulate ROW NUMBER   using a table variable with an IDENTITY column   DECLARE  pageNo int -- 1 based DECLARE  pageSize int SET  pageNo   51 SET  pageSize   20  DECLARE  firstRecord int DECLARE  lastRecord int SET  firstRecord     pageNo - 1     pageSize   1 -- 1001 SET  lastRecord    firstRecord    pageSize - 1   -- 1020  DECLARE  orderedKeys TABLE     rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED    TableKey int NOT NULL    SET ROWCOUNT  lastRecord INSERT INTO  orderedKeys  TableKey  SELECT ID FROM Orders WHERE OrderDate  gt    1980-01-01  ORDER BY OrderDate  SET ROWCOUNT 0  SELECT t   FROM Orders t   INNER JOIN  orderedKeys o ON o TableKey   t ID WHERE o rownum  gt    firstRecord ORDER BY o rownum   This approach can be extended to tables with multi-column keys  and it doesn t incur the performance overhead of using OR  which skips index usage    The downside is the amount of temporary space used up if the data set is very large and one is near the last page   I did not test cursor performance in that case  but it might be better   Note that this approach could be optimized for the first page of data   Also  ROWCOUNT was used since TOP does not accept a variable in SQL Server 2000

User · Answer

CREATE view vw sppb part listsource as      select row number   over  partition by sppb part init id order by sppb part sppb part id asc   as idx    from         select            part SPPB PART ID             0 as is rev             part part number              part init id        from t sppb init part part        left join t sppb init partrev prev on   part SPPB PART ID   prev SPPB PART ID         where prev SPPB PART ID is null        union        select            part SPPB PART ID             1 as is rev             prev part number              part init id        from t sppb init part part        inner join t sppb init partrev prev on   part SPPB PART ID   prev SPPB PART ID         sppb part   will restart idx when it comes to different init id

User · Answer

Finally  Microsoft SQL Server 2012 was released  I really like its simplicity for a pagination  you don t have to use complex queries like answered here    For getting the next 10 rows just run this query   SELECT   FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY    https   docs microsoft com en-us sql t-sql queries select-order-by-clause-transact-sql using-offset-and-fetch-to-limit-the-rows-returned  Key points to consider when using it    ORDER BY is mandatory to use OFFSET     FETCH clause  OFFSET clause is mandatory with FETCH  You cannot use ORDER BY     FETCH  TOP cannot be combined with OFFSET and FETCH in the same query expression

User · Answer

Well I have used the following sample query in my SQL 2000 database  it works well for SQL 2005 too  The power it gives you is dynamically order by using multiple columns   I tell you     this is powerful          ALTER PROCEDURE  dbo   RE ListingReports SelectSummary     CompanyID  int   pageNumber     int   pageSize   int    sort       varchar 200  AS  DECLARE  sql nvarchar 4000  DECLARE  strPageSize nvarchar 20  DECLARE  strSkippedRows nvarchar 20  DECLARE  strFields nvarchar 4000  DECLARE  strFilter nvarchar 4000  DECLARE  sortBy nvarchar 4000  DECLARE  strFrom nvarchar 4000  DECLARE  strID nvarchar 100   If  pageNumber  lt  0    SET  pageNumber   1 SET  strPageSize   CAST  pageSize AS varchar 20    SET  strSkippedRows   CAST    pageNumber - 1     pageSize  AS varchar 20  -- For    example if pageNumber is 5  pageSize is 10  then SkippedRows   40  SET  strID    ListingDbID  SET  strFields    ListingDbID  ListingID     ExtraRoom    SET  strFrom     vwListingSummary    SET  strFilter     WHERE         CompanyID       CAST  CompanyID As varchar 20    End SET  sortBy      if len ltrim rtrim  sort     gt  0  SET  sortBy     Order By      sort  -- Total Rows Count  SET  sql     SELECT Count      strID       FROM      strFROM    strFilter EXEC sp executesql  sql  --   This technique is used in a Single Table pagination SET  sql    SELECT      strFields     FROM      strFROM         WHERE      strID      IN             SELECT TOP      strPageSize          strID     FROM      strFROM    strFilter                   AND       strID     NOT IN                  SELECT TOP      strSkippedRows          strID     FROM      strFROM    strFilter    SortBy               SortBy           SortBy Print  sql  EXEC sp executesql  sql   The best part is sp executesql caches later calls  provided you pass same parameters i e generate same sql text

User · Answer

Use case wise the following seem to be easy to use and fast  Just set the page number   use AdventureWorks DECLARE  RowsPerPage INT   10   PageNumber INT   6  with result as  SELECT SalesOrderDetailID  SalesOrderID  ProductID  ROW NUMBER   OVER  ORDER BY SalesOrderDetailID  AS RowNum FROM Sales SalesOrderDetail where 1 1   select SalesOrderDetailID  SalesOrderID  ProductID from result WHERE result RowNum BETWEEN    PageNumber-1   RowsPerPage  1 AND  RowsPerPage   PageNumber    also without CTE  use AdventureWorks DECLARE  RowsPerPage INT   10   PageNumber INT   6 SELECT SalesOrderDetailID  SalesOrderID  ProductID FROM   SELECT SalesOrderDetailID  SalesOrderID  ProductID  ROW NUMBER   OVER  ORDER BY SalesOrderDetailID  AS RowNum FROM Sales SalesOrderDetail where 1 1    AS SOD WHERE SOD RowNum BETWEEN    PageNumber-1   RowsPerPage  1 AND  RowsPerPage   PageNumber

User · Answer

The best way for paging in sql server 2012 is by using offset and fetch next in a stored procedure  OFFSET Keyword -  If we use offset with the order by clause then the query will skip the number of records we specified in OFFSET n Rows   FETCH NEXT Keywords - When we use Fetch Next with an order by clause only it will returns the no of rows you want to display in paging  without Offset then SQL will generate an error  here is the example given below   create procedure sp paging     pageno as int    records as int   as begin declare  offsetcount as int set  offsetcount   pageno-1   records select id bs variable from salary order by id offset  offsetcount rows fetch Next  records rows only end   you can execute it as follow   exec sp paging 2 3

User · Answer

This is a duplicate of the 2012 old SO question  efficient way to implement paging     FROM  TableX    ORDER BY  FieldX    OFFSET 500 ROWS   FETCH NEXT 100 ROWS ONLY   Here the topic is discussed in greater details  and with alternate approaches

User · Answer

There is a good overview of different paging techniques at http   www codeproject com KB aspnet PagingLarge aspx  I ve used ROWCOUNT method quite often mostly with SQL Server 2000  will work with 2005  amp  2008 too  just measure performance compared to ROW NUMBER   it s lightning fast  but you need to make sure that the sorted column s  have   mostly  unique values

User · Answer

You didn t specify the language nor which driver you are using  Therefore I m describing it abstractly    Create a scrollable resultset   dataset  This required a primary on the table s  jump to the end request the row count  jump to the start of the page scroll through the rows until the end of the page

User · Answer

This bit gives you ability to paginate using SQL Server  and newer versions of MySQL and carries the total number of rows in every row  Uses your pimary key to count number of unique rows   WITH T AS       SELECT TABLE ID  ROW NUMBER   OVER  ORDER BY TABLE ID  AS RN      SELECT COUNT TABLE ID  FROM TABLE  AS TOTAL    FROM TABLE  NOLOCK     SELECT T2 FIELD1  T2 FIELD2  T2 FIELD3  T TOTAL  FROM TABLE T2  NOLOCK  INNER JOIN T ON T2 TABLE ID T TABLE ID WHERE T RN  gt   100 AND T RN  lt  200

User · Answer

MSDN  ROW NUMBER  Transact-SQL      Returns the sequential number of a row within a partition of a result set  starting at 1 for the first row in each partition       The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate    WITH OrderedOrders AS       SELECT         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  gt  50 AND RowNumber  lt  60        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

User · Answer

For the ROW NUMBER technique  if you do not have a sorting column to use  you can use the CURRENT TIMESTAMP as follows   SELECT TOP 20      col1      col2      col3      col4 FROM       SELECT           tbl col1 AS col1          tbl col2 AS col2          tbl col3 AS col3          tbl col4 AS col4          ROW NUMBER   OVER               ORDER BY CURRENT TIMESTAMP               AS sort row     FROM dbo MyTable tbl       AS query WHERE query sort row  gt  10 ORDER BY query sort row   This has worked well for me for searches over table sizes of even up to 700 000    This fetches records 11 to 30

User · Answer

These are my solutions for paging the result of query in SQL server side  these approaches are different between SQL Server 2008 and 2012  Also  I have added the concept of filtering and order by with one column  It is very efficient when you are paging and filtering and ordering in your Gridview   Before testing  you have to create one sample table and insert some row in this table    In real world you have to change Where clause considering your table fields and maybe you have some join and subquery in main part of select   Create Table VLT       ID int IDentity 1 1       Name nvarchar 50       Tel Varchar 20    GO   Insert INTO VLT VALUES       NAME    Convert varchar 10    identity   FAMIL    Convert varchar 10    identity   GO 500000   In all of these sample  I want to query 200 rows per page and I am fetching the row for page number 1200   In SQL server 2008  you can use the CTE concept  Because of that  I have written two type of query for SQL server 2008   -- SQL Server 2008   DECLARE  PageNumber Int   1200 DECLARE  PageSize INT   200 DECLARE  SortByField int   1 --The field used for sort by DECLARE  SortOrder nvarchar 255     ASC  --ASC or DESC DECLARE  FilterType nvarchar 255     None  --The filter type  as defined on the client side  None Contain NotContain Match NotMatch True False   DECLARE  FilterValue nvarchar 255       --The value the user gave for the filter DECLARE  FilterColumn int   1 --The column to wich the filter is applied  represents the column number like when we send the information   SELECT    Data ID    Data Name    Data Tel FROM           SELECT        ROW NUMBER            OVER  ORDER BY                  CASE WHEN  SortByField   1 AND  SortOrder    ASC                        THEN VLT ID END ASC                  CASE WHEN  SortByField   1 AND  SortOrder    DESC                        THEN VLT ID END DESC                  CASE WHEN  SortByField   2 AND  SortOrder    ASC                        THEN VLT Name END ASC                  CASE WHEN  SortByField   2 AND  SortOrder    DESC                        THEN VLT Name END ASC                  CASE WHEN  SortByField   3 AND  SortOrder    ASC                        THEN VLT Tel END ASC                  CASE WHEN  SortByField   3 AND  SortOrder    DESC                        THEN VLT Tel END ASC            AS RowNum                FROM VLT      WHERE         -- We apply the filter logic here         CASE           WHEN  FilterType    None  THEN 1            -- Name column filter           WHEN  FilterType    Contain  AND  FilterColumn   1             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT ID LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    NotContain  AND  FilterColumn   1             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT ID NOT LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    Match  AND  FilterColumn   1             AND VLT ID    FilterValue THEN 1           WHEN  FilterType    NotMatch  AND  FilterColumn   1             AND VLT ID  lt  gt   FilterValue THEN 1                           -- Name column filter           WHEN  FilterType    Contain  AND  FilterColumn   2             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Name LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    NotContain  AND  FilterColumn   2             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Name NOT LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    Match  AND  FilterColumn   2             AND VLT Name    FilterValue THEN 1           WHEN  FilterType    NotMatch  AND  FilterColumn   2             AND VLT Name  lt  gt   FilterValue THEN 1                    -- Tel column filter             WHEN  FilterType    Contain  AND  FilterColumn   3             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Tel LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    NotContain  AND  FilterColumn   3             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Tel NOT LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    Match  AND  FilterColumn   3             AND VLT Tel    FilterValue THEN 1           WHEN  FilterType    NotMatch  AND  FilterColumn   3             AND VLT Tel  lt  gt   FilterValue THEN 1              END           1        AS Data WHERE Data RowNum  gt   PageSize     PageNumber - 1    AND Data RowNum  lt    PageSize    PageNumber ORDER BY Data RowNum  GO   And second solution with CTE in SQL server 2008   DECLARE  PageNumber Int   1200 DECLARE  PageSize INT   200 DECLARE  SortByField int   1 --The field used for sort by DECLARE  SortOrder nvarchar 255     ASC  --ASC or DESC DECLARE  FilterType nvarchar 255     None  --The filter type  as defined on the client side  None Contain NotContain Match NotMatch True False   DECLARE  FilterValue nvarchar 255       --The value the user gave for the filter DECLARE  FilterColumn int   1 --The column to wich the filter is applied  represents the column number like when we send the information    WITH   Data CTE   AS           SELECT        ROW NUMBER            OVER  ORDER BY                  CASE WHEN  SortByField   1 AND  SortOrder    ASC                        THEN VLT ID END ASC                  CASE WHEN  SortByField   1 AND  SortOrder    DESC                        THEN VLT ID END DESC                  CASE WHEN  SortByField   2 AND  SortOrder    ASC                        THEN VLT Name END ASC                  CASE WHEN  SortByField   2 AND  SortOrder    DESC                        THEN VLT Name END ASC                  CASE WHEN  SortByField   3 AND  SortOrder    ASC                        THEN VLT Tel END ASC                  CASE WHEN  SortByField   3 AND  SortOrder    DESC                        THEN VLT Tel END ASC            AS RowNum                FROM VLT     WHERE         -- We apply the filter logic here         CASE           WHEN  FilterType    None  THEN 1            -- Name column filter           WHEN  FilterType    Contain  AND  FilterColumn   1             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT ID LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    NotContain  AND  FilterColumn   1             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT ID NOT LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    Match  AND  FilterColumn   1             AND VLT ID    FilterValue THEN 1           WHEN  FilterType    NotMatch  AND  FilterColumn   1             AND VLT ID  lt  gt   FilterValue THEN 1                           -- Name column filter           WHEN  FilterType    Contain  AND  FilterColumn   2             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Name LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    NotContain  AND  FilterColumn   2             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Name NOT LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    Match  AND  FilterColumn   2             AND VLT Name    FilterValue THEN 1           WHEN  FilterType    NotMatch  AND  FilterColumn   2             AND VLT Name  lt  gt   FilterValue THEN 1                    -- Tel column filter             WHEN  FilterType    Contain  AND  FilterColumn   3             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Tel LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    NotContain  AND  FilterColumn   3             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Tel NOT LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    Match  AND  FilterColumn   3             AND VLT Tel    FilterValue THEN 1           WHEN  FilterType    NotMatch  AND  FilterColumn   3             AND VLT Tel  lt  gt   FilterValue THEN 1              END           1           SELECT    Data ID    Data Name    Data Tel FROM Data CTE AS Data WHERE Data RowNum  gt   PageSize     PageNumber - 1    AND Data RowNum  lt    PageSize    PageNumber ORDER BY Data RowNum   -- SQL Server 2012   DECLARE  PageNumber Int   1200 DECLARE  PageSize INT   200 DECLARE  SortByField int   1 --The field used for sort by DECLARE  SortOrder nvarchar 255     ASC  --ASC or DESC DECLARE  FilterType nvarchar 255     None  --The filter type  as defined on the client side  None Contain NotContain Match NotMatch True False   DECLARE  FilterValue nvarchar 255       --The value the user gave for the filter DECLARE  FilterColumn int   1 --The column to wich the filter is applied  represents the column number like when we send the information    WITH   Data CTE   AS           SELECT                FROM VLT     WHERE         -- We apply the filter logic here         CASE           WHEN  FilterType    None  THEN 1            -- Name column filter           WHEN  FilterType    Contain  AND  FilterColumn   1             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT ID LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    NotContain  AND  FilterColumn   1             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT ID NOT LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    Match  AND  FilterColumn   1             AND VLT ID    FilterValue THEN 1           WHEN  FilterType    NotMatch  AND  FilterColumn   1             AND VLT ID  lt  gt   FilterValue THEN 1                           -- Name column filter           WHEN  FilterType    Contain  AND  FilterColumn   2             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Name LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    NotContain  AND  FilterColumn   2             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Name NOT LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    Match  AND  FilterColumn   2             AND VLT Name    FilterValue THEN 1           WHEN  FilterType    NotMatch  AND  FilterColumn   2             AND VLT Name  lt  gt   FilterValue THEN 1                    -- Tel column filter             WHEN  FilterType    Contain  AND  FilterColumn   3             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Tel LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    NotContain  AND  FilterColumn   3             AND   -- In this case  when the filter value is empty  we want to show everything                  VLT Tel NOT LIKE        FilterValue                      OR                  FilterValue                       THEN 1           WHEN  FilterType    Match  AND  FilterColumn   3             AND VLT Tel    FilterValue THEN 1           WHEN  FilterType    NotMatch  AND  FilterColumn   3             AND VLT Tel  lt  gt   FilterValue THEN 1              END           1               SELECT    Data ID    Data Name    Data Tel FROM Data CTE AS Data ORDER BY      CASE WHEN  SortByField   1 AND  SortOrder    ASC          THEN Data ID END ASC      CASE WHEN  SortByField   1 AND  SortOrder    DESC          THEN Data ID END DESC      CASE WHEN  SortByField   2 AND  SortOrder    ASC          THEN Data Name END ASC      CASE WHEN  SortByField   2 AND  SortOrder    DESC          THEN Data Name END ASC      CASE WHEN  SortByField   3 AND  SortOrder    ASC          THEN Data Tel END ASC      CASE WHEN  SortByField   3 AND  SortOrder    DESC          THEN Data Tel END ASC OFFSET  PageSize     PageNumber - 1  ROWS FETCH NEXT  PageSize ROWS ONLY

User · Answer

Try this approach   SELECT TOP  offset a   FROM  select top  limit b    COUNT    OVER   totalrows          from TABLENAME b order by id asc  a ORDER BY id desc

User · Answer

Incredibly  no other answer has mentioned the fastest way to do pagination in all SQL Server versions  Offsets can be terribly slow for large page numbers as is benchmarked here  There is an entirely different  much faster way to perform pagination in SQL  This is often called the  seek method  or  keyset pagination  as described in this blog post here   SELECT TOP 10 first name  last name  score  COUNT    OVER   FROM players WHERE  score  lt   previousScore     OR  score    previousScore AND player id  lt   previousPlayerId  ORDER BY score DESC  player id DESC   The  seek predicate   The  previousScore and  previousPlayerId values are the respective values of the last record from the previous page  This allows you to fetch the  next  page  If the ORDER BY direction is ASC  simply use  gt  instead   With the above method  you cannot immediately jump to page 4 without having first fetched the previous 40 records  But often  you do not want to jump that far anyway  Instead  you get a much faster query that might be able to fetch data in constant time  depending on your indexing  Plus  your pages remain  stable   no matter if the underlying data changes  e g  on page 1  while you re on page 4    This is the best way to implement pagination when lazy loading more data in web applications  for instance   Note  the  seek method  is also called keyset pagination   Total records before pagination  The COUNT    OVER   window function will help you count the number of total records  before pagination   If you re using SQL Server 2000  you will have to resort to two queries for the COUNT

User · Answer

From 2012 onward we can use  OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

[sql] What is the best way to paginate results in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to performance

[sql] What is the best way to paginate results in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to performance

Examples related to pagination