How to implement Custom Paging with SQL Server using row_number() method

Huge data with paging always causes big headaches to developers. It not only slows down the performance of the application but also takes up lot of resources of the server.

But fortunately you can use the technique of custom paging in your SQL server stored procedure that make your application faster and better.

Custom Paging Syntax

Select * From (Select ROW_NUMBER() Over (OrderByClause) AS 'RowNum',* 
From TableName WhereConditions)t Where t.RowNum Between
((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)

In the above syntax you have to provide the SQL Order By clause for OrderByClause and give your conditions through SQL Where Clause for the WhereConditions.

Note that the TableName is the database table.

If you don’t have any Where Conditions then omit the WhereConditions.

Custom Paging Example in Northwind Database

Let me explain you how Custom Paging will work for the Products Table in Northwind Database.

The Products table is shown below:

Now suppose I want to get those products from this table:

• ♀ in newest-first order
• ♀ whose UnitsInStock are greater than 10

So my Custom Paging Query will become –

Select * From (Select ROW_NUMBER() Over (Order by ProductId Desc) AS 'RowNum',* 
From Products Where UnitsInStock > 10)t Where t.RowNum Between
((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)

To test this Query let me execute it by supplying it with Page Number and Size.

Execute the below Query to get page number 1 records.

Declare @PageNo INT
Declare @PageSize INT
Set @PageNo=1
Set @PageSize=4
Select * From (Select ROW_NUMBER() Over (Order by ProductId Desc) AS 'RowNum',* 
From Products Where UnitsInStock > 10)t Where t.RowNum Between
((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)

You can clearly see the query is giving me newest records (product id 77, 76, 75 ,74). Also the RowNum given to these records are (1, 2, 3, 4).

The 2nd page query will be:

Declare @PageNo INT
Declare @PageSize INT
Set @PageNo=2
Set @PageSize=4
Select * From (Select ROW_NUMBER() Over (Order by ProductId Desc) AS 'RowNum',* 
From Products Where UnitsInStock > 10)t Where t.RowNum Between
((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)

It gives:

Here I get RowNum from 5 to 8.

This table has 77 records so if I keep page size as 4 then the last page would be 16.

So the query for last page would be:

Declare @PageNo INT
Declare @PageSize INT
Set @PageNo=16
Set @PageSize=4
Select * From (Select ROW_NUMBER() Over (Order by ProductId Desc) AS 'RowNum',* 
From Products Where UnitsInStock > 10)t Where t.RowNum Between
((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)

I get just 3 records on the last page with RowNum as 61, 62, 63.

Custom Paging Dynamic Query Example

In custom paging, the SQL Script returns only the records of the current page. This greatly reduces the size of the returned data unlike the non-custom paging approach where the whole of the data is returned.

For example, suppose for an eCommerce website I have a table named ProductOrder which stores the all the orders received by the eCommerce website.

The table has the columns stated below –

Note that column OrderId is an identity column and OrderDate is given default value binding by the getdate() method.

Now suppose you have to get all the orders of a particular date range based on custom paging. In this case create a Stored Procedure which implements the custom paging logic.

The Stored Procedure is given below –

CREATE PROCEDURE [dbo].[sp_OrderCustomPaging]
 @StartDate    DATETIME,
 @EndDate      DATETIME,
 @PageNo       INT,
 @PageSize     INT
AS     
 SET NOCOUNT ON;      
 DECLARE @qry  NVARCHAR(2000);      
 DECLARE @qryMain  NVARCHAR(2000);  
BEGIN     
   SET @qry='SELECT ROW_NUMBER() OVER (Order By OrderDate desc) AS ''RowNum'',* FROM PRODUCTORDER WHERE 1=1'
    
   IF @StartDate IS NOT NULL 
       SET @qry=@qry+' and CONVERT(date, OrderDate)>='''+CONVERT(NVARCHAR(25),CONVERT(DATE,@StartDate))+''''     
   IF @EndDate IS NOT NULL     
       SET @qry=@qry+' and CONVERT(date, OrderDate)<='''+CONVERT(NVARCHAR(25),CONVERT(DATE,@EndDate))+''''     
    
   SET @qryMain='SELECT * FROM('+@qry+')a WHERE a.RowNum BETWEEN ('+CONVERT(VARCHAR(10),@pageNo)+'-1)*'+CONVERT(VARCHAR(10),@pageSize)+'+1 AND ('+CONVERT(VARCHAR(10),@pageNo)+'* '+CONVERT(VARCHAR(10),@pageSize)+')'
    
   SET @qryMain=@qryMain +';SELECT COUNT(*) AS ''Total'' FROM('+@qry+')a'
   EXEC sp_executesql @qryMain     
End

In the stored procedure I am passing 4 parameters which are –

• 1. @StartDate – the starting date for which I have to get orders.
• 2. @EndDate – the end date for which I have to get orders.
• 3. @PageNo – the page number for which the orders have to be taken.
• 4. @PageSize – the page size.

SQL Server Row_Number

Our stored procedure uses SQL ROW_NUMBER() function to give each row of the order subset (based on start date and end date), a row value and then it returns only those rows which comes for the particular page.

The page number and page size are passed to the stored procedure as parameters.

I have used dynamic query in our stored procedure and finally executed the dynamic query to provide all the records of the particular page.

It is also important to note that the stored procedure is also returning the total no of orders on the date range. This will help our application to create all the paging links correctly.

Now with our custom paging stored procedure in place you can simple call the procedure from our application page and make the application faster and better.