Data Pagination in the stored procedure

First of all why do we need to do our paging in the back end when all the data controls in ASP.NET provide paging technique?  The answer is simple, to improve the performance of the application. In small applications you many not find the difference, but in big applications where thousand to millions records need to be retrieved, Data controls pagination takes 2 – 5 minutes which is not acceptable at all, and even you may get out of memory. So it’s always a good idea to have your paging mechanism on the  backend. In this article i will explain the way how we can do our paging on backend by sending page Size & current Page.

We will create a stored procedure which takes page Size ( number of records to be shown in one page ) & current page and will return records from the log table of our database.

CREATE PROCEDURE dbo.ShowLog
                    @currentPage INT,
                    @pageSize INT
        AS 

        BEGIN 

        WITH tempLog AS (
        SELECT ROW_NUMBER() AS Row, Date, Description
        FROM LOG) 

        SELECT Date, Description
        FROM tempLog
        WHERE Row between ((@currentPage - 1) * @pageSize + 1) and (@currentPage*@pageSize)

        END

We have used ROW_NUMBER() to get the records which we required. Row_number() can be used in SQL server 2005 and later versions of it.Using Row_number() we can give number to the records based on over class, if there is no over class used then default order will be considered. Now we will see how to use over clause with Row_number().

SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)AS Row,
	   Date, Description
FROM LOG

If you see above line we have used OVER class on Row_Number(), so sequence numbering will be done in the order of Date ( descending order). So this helps us to get subset of records which are sorted by few columns.

Even we can use the above stored procedure to get the full records from Log Table, In some cases you might want to get full records into your Data set or you want to export entire data to Excel sheet or so. In that case you require to get full records and at them same time you have to apply paging in your Grid control. So at a time we require both cases, you can achieve that by changing the above code WHERE class into,

WHERE
                (@currentPage > 0
                AND @pageSize > 0
                AND Row between ((@currentPage - 1) * @pageSize + 1) and @currentPage*@PageSize
                OR (@currentPage=0 AND @pageSize=0)) 

In the above code if you want to get full records at a time send pageSize & currentPage values “0” as parameters. So by using the above approach you can improve the performance of your applications.