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.
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.
— Add the parameters for the stored procedure here
@startRowIndex int ,
— SET NOCOUNT ON added to prevent extra result sets from — interfering with SELECT statements. SETNOCOUNT ON ;
— increase the startRowIndex by 1 to avoid returning the last record again
[email protected] = @startRowIndex + 1
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
In the above stored procedure, i have two parameters (@startRowIndex, @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 OrderBy statement same in both subqueries like i have specified as "Order By AutoID ASC". Now you can writeyour ASP.NET code that access the method that accepts two parameters and give you the desired result.
In my above example, i will excute the stored procedure with following parameter values for retreiving different rows/pages of records. I am assuming that you need to show 5 records per page.
Page 1 – EXEC LoadPagesArticles 0,5
Page 2 – EXEC LoadPagesArticles 5,5
Page 3 – EXEC LoadPagesArticles 10,5
It is always good to write code that is efficient and fast to serve the user request, Custom paging is most efficient way to get desired number of records from a large database tabe without hitting the performance of the database as well as application.