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