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.

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 (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – Tricks for Row Offset and Paging in Various Versions of SQL Server

  1. 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

  2. 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

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

  4. 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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s