SQL SERVER – Row Offset in SQL Server For Different Version

A very common question is how to find row offset in SQL Server. Here are a few examples based on different version of SQL Server. I have included SQL Server 2000 event, though it is almost 15 years old product and I encounter is less and less every day on production server.

I have used database adventureworks for example.

USE AdventureWorks2014
GO
--------------------------------------------------
-- SQL Server 2012/2014
--------------------------------------------------
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
--------------------------------------------------i
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
DECLARE
@PageNumber INT
SET
@RowsPerPage = 10
SET @PageNumber = 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

The result of the above queries is identical to each other.

SQL SERVER - Row Offset in SQL Server For Different Version paginginversion

This blog post is based on an earlier blog post which had a few errors corrected in this blog.

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

Previous Post
SQL SERVER – ​Tuning Queries is Sometimes Waste of Time – Notes from the Field #049
Next Post
SQL SERVER – Add Auto Incremental Identity Column to Table After Creating Table

Related Posts

No results found.

1 Comment. Leave new

  • Hi,
    Regarding Sql 2000 query.It is giving correct output because there is no gap in SalesOrderDetailID ,so it working like row number itself.In case there is gap in column valu then it won’t work .First of all you hv to create rownumber ,either thru query or insert into temp table with identity on then query on temp table .
    In sql 2000,row number can be created like this and apply offset,

    SELECT
    (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail B WHERE B.SalesOrderDetailID<=A.SalesOrderDetailID)RowNum,
    SalesOrderDetailID, SalesOrderID, ProductID
    FROM Sales.SalesOrderDetail A
    ) TBL

    WHERE RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1
    AND @RowsPerPage*(@PageNumber)
    ORDER BY SalesOrderDetailID

    Because of count and lot of records this query is very slow.So safe bet is to create temp table with identity.insert into temp table then query on temp table applying offset.

    Thanks

    Reply

Leave a Reply

Menu