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 (https://blog.sqlauthority.com)
19 Comments. Leave new
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
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
Good one .
Just clear about the sql serfver 2012.[simplest of all]
Is it using cursor sort of thing? [FETCH NEXT in the syntax]
How can we get total number of rows count using OFFSET FETCH NEXT in sql server 2012>
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
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;
Will it work for pagenumber = 1?
(@RowsPerPage) showing error in Sql Server 2000
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 :)
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 :)
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?
Do you have a sample which you can share to reproduce the behavior?
OFFSET (@PageNumber-1)*@RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY
GO
This is not working when the table has no unique key
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
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?
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;
Very interesting.
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.
I LOVE YOU