Server Side Pagination Query

The paging of a large database resultset in Web applications is a well known problem. In short, you don't want all the results from your query to be displayed on a single Web page, so some sort of paged display is more appropriate. While it was not an easy task in the old ASP, the DataGrid control in the ASP.NET simplifies this to a few lines of code. So, the paging is easy in ASP.NET, but the default behavior of the DataGrid is that all resulting records from your query will be fetched from SQL server to the ASP.NET application. If your query returns a million records this will cause some serious performance issues (if you need convincing, try executing such a query in your web application and see the memory consumption of the aspnet_wp.exe in the task manager). That's why a custom paging solution is required where desired behavior is to fetch only the rows from the current page. 

Here I provide some query which provide solution for custom pagination. It helps to improve your search result for your filter data.

Code

 

use [Northwind]

Go

create procedure [dbo].[sp_OrderDetail_pagin]

--parameter declaration

@pageno int = 1,--page no

@pagesize int = 1 -- page size

AS

SET NOCOUNT ON

BEGIN

--check if page no 1 then send only top no of record as per page size

--Generally people visit first page of any table. By this condition 1st page load very fast.

if @pageno = 1

begin

      SELECT top (@pagesize )

            Orders.OrderID,  Orders.OrderDate, [Order Details].ProductID,Products.ProductName,

            [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,

---- for total record use count(1) over() function

COUNT(1) over() as totalCount        

            FROM         Orders INNER JOIN

                      [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN

                      Products ON [Order Details].ProductID = Products.ProductID

ORDER BY Orders.OrderDate desc

end

else

--for other page use with (CTE) query , for better performance fetch only (pageno * pagesize) records

begin

      WITH SearchResults  (OrderID,  OrderDate, ProductID,ProductName,UnitPrice,Quantity, Discount, totalCount,RowNum) AS (

            SELECT top (@pageno * @pagesize )

            Orders.OrderID,  Orders.OrderDate, [Order Details].ProductID,Products.ProductName,

            [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, COUNT(1) over(),

            ROW_NUMBER() OVER (ORDER BY Orders.OrderDate desc) AS RowNum

            FROM         Orders INNER JOIN

                      [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN

                      Products ON [Order Details].ProductID = Products.ProductID

      )

--for get records as per pagesize

            SELECT * FROM SearchResults

            WHERE RowNum BETWEEN ((@pagesize * (@pageno-1)) +1)  AND @pagesize * @pageno

end

END