Implementing Custom Paging in ASP.NET with SQL Server

Introduction

Why Custom Paging?
Custom paging allows you to get limited number records from a large database table that saves processing time of your database server as well as your application server and makes your application scalable, efficient and fast.

In this article, I am going to explain how to create a stored procedure in SQL Server 2005 that allows you to pass startRowIndex and pageSize as a parameter and return you the number of records starting from that row index to the page size specified. It was possible in the SQL Server 2000 too but it was not as easy as in SQL Server 2005 is.

Stored Procedure for Custom Paging in SQL Server 2005

In this example, I am assuming that I have a Articles table that contains thousands of records and I have to list articles from this table page wise. To do that I am going to pass two parameter to this SP and getting the custom paging done.

-- EXEC LoadPagedArticles 10, 5
CREATE PROCEDURE LoadPagedArticles 
-- Add the parameters for the stored procedure here
@startRowIndex int,
@pageSize int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- increase the startRowIndex by 1 to avoid returning the last record again

SET @startRowIndex = @startRowIndex + 1 
 BEGIN
       SELECT * FROM (
                Select *, 
                ROW_NUMBER() OVER (ORDER BY AutoID ASC) as RowNum
                FROM Articles 
              ) as ArticleList
       WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
       ORDER BY AutoID ASC
  END
END
GO

In the above stored procedure, I have two parameter @startRowIndex and @pageSize. @startRowIndex receives the row Index from where records to be returned and @pageSize receives the number of records to be returned.

First I am increasing the @startRowIndex by 1 so that I don't get the last record again in my result then writing my select statement specific to paging. Here you need to make sure that you are writing order by statement same in both subquery and main query like I have specified as "ORDER BY AutoID ASC" in my above code snippet. Now you can write your ASP.NET code that access the method that accepts two parameter and give you the desired result.

In my above example, I will execute the SP in SQL Server with followig parameter value for different pages (I am assuming that I have to get 5 records per page), You should pass same value from your code to the SP.

UPDATE: The pagination code in SQL Server 2012 onward has become much easier and efficient.

SELECT * FROM Articles WHERE Active = 1 AND IsPublished = 1 AND
ORDER BY UpdatedDateTime DESC 
OFFSET @startRowIndex ROWS FETCH NEXT @pageSize ROWS ONLY

Page 1 - EXEC LoadPagedArticles 0, 5
Page 2 - EXEC LoadPagedArticles 5, 5
Page 3 - EXEC LoadPagedArticles 10, 5

Conclusion

Its always good to write code that is efficien and fast to serve the user request, Custom paging is most efficient way to get desired number of records from a large database table without hitting the performance of the database as well as application.

Please let me know your feedback if any. Happy Coding!!!