SQL SERVER – Tricks for Row Offset and Paging in Various Versions of SQL Server

Paging is one of the most needed tasks when developers are developing applications. SQL Server has introduced various features of SQL Server 2000 to the latest version of SQL Server 2012. Here is the blog post which I wrote which demonstrates how SQL Server Row Offset and Paging works in various versions of the SQL Server. Instead of giving the generic algorithm, I have used AdventureWorks database and build a script. This will give you better control over your data if you have installed the AdventureWorks database and you can play around with various parameters.

The goal is to retrieve row number 51 to 60 from the table Sales.SalesOrderDetails of database AdventureWorks.

SQL SERVER - Tricks for Row Offset and Paging in Various Versions of SQL Server offsetandpaging

Solarwinds

You can install the AdventureWorks database and you can run following queries based on your version.

USE AdventureWorks2012
GO
--------------------------------------------------
-- SQL Server 2012
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET
(@PageNumber-1)*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO
--------------------------------------------------
-- SQL Server 2008 / R2
-- SQL Server 2005
--------------------------------------------------
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 ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
AND @RowsPerPage*(@PageNumber)
GO
--------------------------------------------------
-- SQL Server 2000
--------------------------------------------------
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
SELECT SalesOrderDetailID, SalesOrderID, ProductID
FROM
(
SELECT TOP (@RowsPerPage)
SalesOrderDetailID, SalesOrderID, ProductID
FROM
(
SELECT TOP ((@PageNumber)*@RowsPerPage)
SalesOrderDetailID, SalesOrderID, ProductID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
) AS SOD
ORDER BY SalesOrderDetailID DESC
) AS SOD2
ORDER BY SalesOrderDetailID ASC
GO

I am sure there are better and efficient ways but the tricks demonstrated above just works. Please feel free to leave a comment with your suggestions.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Weekly Series – Memory Lane – #024
Next Post
SQLAuthority News – Excellent Experience at TechEd India 2013 Bangalore and Pune – Photo Journey

Related Posts

19 Comments. Leave new

  • Dattatraya Kale (@way2datta)
    April 14, 2013 5:35 pm

    Hi Pinal,

    Thanks for sharing your thoughts :)

    can you please tell me whether following way of paging and sorting is correct?

    tell me drawbacks of it.

    create table #Student
    (id int identity(1,1), name nvarchar(100), birthdate datetime)
    declare @PageSize int=5
    declare @MinRowNumber int=1
    declare @MaxRowNumber int= @MinRowNumber + @PageSize-1
    declare @SortExpression nvarchar(100)=’Name’ —‘birthdate’ Pass here column name.

    declare @SortOrder nvarchar(100)=’D’ –‘A’ pass here sort order D- for descending and A for ascending.
    insert #Student
    select ‘Viru’,’1986-12-07′
    union all
    select ‘Sachin’,’1960-01-31′
    union all
    select ‘Gambhir’,’1950-06-23′
    union all
    select ‘Shreekant’,’1983-10-04′
    union all
    select ‘Kapil’,’1981-03-17′
    union all
    select ‘Yuvi’,’1986-03-02′
    union all
    select ‘Dhoni’,’1987-12-05′
    union all
    select ‘Zak’,’1975-08-17′
    union all
    select ‘Bhajji’,’2007-08-06′
    union all
    select ‘Ashwin’,’2010-04-22′
    union all
    select ‘Ojha’,’2012-02-26′
    union all
    select ‘Ravindra’,’2012-10-04′

    SELECT RowNumber, name, birthdate,id FROM
    (select *,ROW_NUMBER() OVER (ORDER BY
    CASE
    WHEN @SortExpression = ‘Name’ AND @SortOrder=’D’ THEN Name END DESC,
    CASE
    WHEN @SortExpression = ‘Name’ AND @SortOrder=’A’ THEN Name END ASC,
    CASE
    WHEN @SortExpression = ‘birthdate’ AND @SortOrder=’D’ THEN birthdate END DESC,
    CASE
    WHEN @SortExpression = ‘birthdate’ AND @SortOrder=’A’ THEN birthdate END ASC
    ) AS RowNumber from #Student) AS Student
    WHERE RowNumber BETWEEN @MinRowNumber AND @MaxRowNumber

    drop table #student

    Reply
  • Hi Dattatraya,
    — This will select only first record to @MinRowNumber + @PageSize-1
    declare @MinRowNumber int=1
    declare @MaxRowNumber int= @MinRowNumber + @PageSize-1

    — This is feasible solution for Paging (i.e. if you want to retrieve results for 2nd page with 10 records per page…., then @pageNumber = 2, @RowsPerPage = 10)
    BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
    AND @RowsPerPage*(@PageNumber)

    In the case of 2nd page records, your query will return incorrect results

    Reply
  • Good one .
    Just clear about the sql serfver 2012.[simplest of all]
    Is it using cursor sort of thing? [FETCH NEXT in the syntax]

    Reply
  • How can we get total number of rows count using OFFSET FETCH NEXT in sql server 2012>

    Reply
    • First case : make an outer select clause with count to get the number of rows returned. E.g

      SELECT COUNT(result.yourcolumnname) FROM
      (
      SELECT * FROM yourtable AS P
      ORDER BY yourcolumn
      OFFSET 1340 ROWS
      FETCH NEXT 1000 ROWS ONLY) as result

      Reply
  • Existe alguna forma de retornar con OFFSET y FETCH el número de registros existente en la consulta “sin haber realizado el pagineo”… Actualmente tengo que hacer la misma consulta 2 veces pero no se ve muy bien “a pesar que sigue siendo mas rapido que usando Row_Number con el CTE:

    Declare @page Int = 3
    Declare @pageSize Int = 25
    Select UsuarioId, UserName, Email From Usuarios
    Order By UsuarioId
    Offset(@page – 1) * @pageSize Rows
    Fetch Next @pageSize Rows Only;
    Select Count(*) From Usuarios;

    Reply
  • Will it work for pagenumber = 1?

    Reply
  • Navneet Kumar
    July 29, 2014 5:51 pm

    (@RowsPerPage) showing error in Sql Server 2000

    Reply
  • Hi Dave,

    Lets say, I have a table “Documents” like this:

    Documents
    —————————————————
    DocID | DocName | …. | DocPageCount
    —————————————————
    Doc001 | ………….|……..| 4
    Doc002 | ………….|……..| 3
    Doc003 | ………….|……..| 7
    Doc004 | ………….|……..| 5
    ….

    Now I want to query the information based on DocPageCount, with paging enabled for the result. Example: I want to get the first page of result (page size: 10), the query should return infomation like this:

    Doc001 | ………….|……..| 4
    Doc002 | ………….|……..| 3
    Doc003 | ………….|……..| 3 *

    It means, the first page of result is counted on the number of pages of documents
    The above result means: for Doc001 and Doc002, all pages available in returned result (total document pages are 7), and for Doc003, its first 3 document pages are returned to come up with total 10 document pages :)

    Do you have any idea how to make it ? :)
    Happy to hear :)

    Reply
  • Hi Dave,

    Lets say, I have a table “Documents” like this:

    Documents
    —————————————————
    DocID | DocName | …. | DocPageCount
    —————————————————
    Doc001 | ………….|……..| 4
    Doc002 | ………….|……..| 3
    Doc003 | ………….|……..| 7
    Doc004 | ………….|……..| 5
    ….

    Now I want to query the information based on DocPageCount, with paging enabled for the result. Example: I want to get the first page of result (page size: 10), the query should return information like this:

    Doc001 | ………….|……..| 4
    Doc002 | ………….|……..| 3
    Doc003 | ………….|……..| 3 *

    It means, the first page of result is counted on the number of pages of documents
    The above result means: for Doc001 and Doc002, all pages available in returned result (total document pages are 7), and for Doc003, its first 3 document pages are returned to come up with total 10 document pages :)

    Do you have any idea how to make it ? :)
    Happy to hear :)

    Reply
  • Michael Adom
    July 7, 2015 5:35 pm

    Hello,

    I have a similar query like yours(2012 version):
    DECLARE @RowsPerPage INT = 10, @PageNumber INT = 6
    SELECT SalesOrderDetailID, SalesOrderID, ProductID
    FROM Sales.SalesOrderDetail
    ORDER BY SalesOrderDetailID
    OFFSET (@PageNumber-1)*@RowsPerPage ROWS
    FETCH NEXT @RowsPerPage ROWS ONLY
    GO

    when the @RowsPerPage is set to a very high number and the records for a particular page is very small, no records are retrieved. Why is it so?

    Reply
  • OFFSET (@PageNumber-1)*@RowsPerPage ROWS
    FETCH NEXT @RowsPerPage ROWS ONLY
    GO
    This is not working when the table has no unique key

    Reply
  • SELECT
    FirstName = R.FirstName,
    LastName = R.LastName,
    CountryId = R.CountryID,
    ……
    FROM Resource AS R
    WHERE …
    ORDER BY LastName, FirstName
    OFFSET 10 * (@PageNumber – 1) ROWS
    FETCH NEXT 10 ROWS ONLY
    The total number of records is over 30,000.

    When @PageNumber = 1, the running time is less than 1s, almost instantly.
    When @PageNumber = 500, the running time is about 4s.
    When @PageNumber = 1000, the running time is less than 12s.
    When @PageNumber = 2000, the running time is less than 20s.
    When @PageNumber = 3000, the running time is less than 28s.

    This offset become very slow

    Reply
  • dario (@rivaldid)
    March 30, 2018 6:31 pm

    Hi mate, could you suggest a variation for sql server 2008 R2 to paginate using the beginning row as input parameter instead of the page number?

    Reply
    • dario (@rivaldid)
      March 30, 2018 9:39 pm

      I found a solution:
      use test

      DECLARE
      @RowsPerPage INT = 10,
      @RowNumber INT = 0+1,
      @PageNumber INT,
      @TableName NVARCHAR(45) = ‘test_table’,
      @NthColumn INT = 0,
      @ColName NVARCHAR(45),
      @sql NVARCHAR(MAX);

      SET @NthColumn = 0 + 1;
      SET @ColName = COL_NAME(OBJECT_ID(@TableName),@NthColumn);
      SET @PageNumber = (CEILING((@RowNumber)/@RowsPerPage)+1);

      SELECT @PageNumber;

      SET @sql = ‘SELECT * FROM (SELECT ‘+QUOTENAME(@TableName)+’.*, ROW_NUMBER() OVER (ORDER BY ‘+QUOTENAME(@ColName)+’ asc) AS RowNum FROM ‘+QUOTENAME(@TableName)+’ WHERE 1=1 ) AS BDT WHERE BDT.RowNum BETWEEN ((‘+CAST(@PageNumber AS VARCHAR)+’-1)*’+CAST(@RowsPerPage AS VARCHAR)+’)+1 AND ‘+CAST(@RowsPerPage AS VARCHAR)+’*(‘+CAST(@PageNumber AS VARCHAR)+’)’; EXEC sp_executesql @sql;

      Reply
  • dario (@rivaldid)
    April 3, 2018 1:41 pm

    My solution came for the serverside pagination of DataTable, which sends to the middleware some different parameter which I need to convert.
    SQL side I get:
    RowsPerPage, the same;
    RowNumber which begin from 0 but ROW_NUMBER() begin from 1;
    NthColumn instead of the column name.
    Btw using this query for my remote resource, it’s seems working good.

    Reply
  • I LOVE YOU

    Reply

Leave a Reply

Menu