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

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.

When working with data stored in SQL Server, you will need the help of sp_executesql statement in order to execute dynamic statements and stored procedures.

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
CRUD operations in SQL Server is the first most tutorial you should read before starting with SQL Server.

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)

Testing By Supplying Page Number and Size

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

Page No 1 and Page Size 4

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)

page number 1 records

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

Page No 2 and Page Size 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:
page number 2 records

Here I get RowNum from 5 to 8.

Page No 16 (last Page) and Page Size 4

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.

page number 2 records

I converted this Custom Paging Query into a Stored Procedure and created ASP.NET GridView Custom Paging.

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 –

Column Name Data Type Properties
OrderId Int Identity
Custom Name Varchar(100)
OrderPrice Money
ProductName Varchar(100)
OrderDate DateTime Default Value getdate()

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 @[email protected]+' and CONVERT(date, OrderDate)>='''+CONVERT(NVARCHAR(25),CONVERT(DATE,@StartDate))+''''      
   IF @EndDate IS NOT NULL      
       SET @[email protected]+' and CONVERT(date, OrderDate)<='''+CONVERT(NVARCHAR(25),CONVERT(DATE,@EndDate))+''''      
   
   SET @qryMain='SELECT * FROM('[email protected]+')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 @[email protected] +';SELECT COUNT(*) AS ''Total'' FROM('[email protected]+')a'
   EXEC sp_executesql @qryMain     
End
Explanation

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.
In SQL Server, other than Backup and Restore of database, there is also an option to Script Database with it’s complete data.

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.

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "WordPress, SEO, jQuery, HTML" and more.