Implementing Custom Paging in ASP.NET with SQL Server

Posted by in Sql Server category on for Advance level | Views : 17700 red flag

Many a times we need to present bulk data to the user in our day to day application development. Loading all data at one shot is okay when we have few hundreads data but when it comes to thousands of thousands records, it hardly work.

In scenario like this we need to go for custom paging in our application.

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


-- Add the parameters for the stored procedure here

@startRowIndex int,

@pageSize int



-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.


-- increase the startRowIndex by 1 to avoid returning the last record again

SET @startRowIndex = @startRowIndex + 1



Select *,


FROM Articles

) as ArticleList

WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1





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.

Notice the last statement in the following code snippet, just we need to keep the OFFSET .... and we are done.
SELECT * FROM Articles WHERE Active = 1 AND IsPublished = 1 AND
ORDER BY UpdatedDateTime DESC 

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


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!!!

Read Cascading delete in SQL Server after this article.
Page copy protected against web site content infringement by Copyscape

About the Author

Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan

Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on | |

Login to vote for this post.

Comments or Responses

Posted by: Dssmanyam on: 7/22/2008
Great Job! You are helping Beginners and also experienced people. Thank You very much
Posted by: Vedantydv123 on: 2/12/2019 | Points: 25
Online now we can learn any things for the knowledge so that you can enjoy this time with a system so please come here and get the help.
Posted by: Vedantydv123 on: 2/12/2019 | Points: 25
Thank you so much for this site.

Login to post response

Comment using Facebook(Author doesn't get notification)