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
CREATE PROCEDURE LoadPagedArticles
-- Add the parameters for the stored procedure here
-- 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
SELECT * FROM (
ROW_NUMBER() OVER (ORDER BY AutoID ASC) as RowNum
) as ArticleList
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1
ORDER BY AutoID ASC
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
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
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!!!