Pagination Stored Procedure in SQL

When listing large amount of data from database, we have to face performance issue in ASP.net pages. There is a default paging mechanism available for some listing controls like Gridview, Listview etc. But all of them have to get total amount of data for manipulation, and from that data they are listing small amount of data using paging mechanism.

 That means, if there are 1 crore rows of data in the table, first of all we need to query out this all records and set to listing control. So it will take very long time to query out this amount of data from the table. 

Better method of pagination in SQL 

So the better mechanism for pagination is, we need to query only small amount of data that we need to display to the user at a time. In order to fetch this function, we have to create a store procedure in SQL with start and end row as parameter. Then If we pass 21 and 30 to the following procedure it will return only 21 to 30 (10 records) and total number of records. So it will be very fast mechanism for pagination.

Pagination Stored Procedure in SQL

CREATE PROCEDURE [dbo].[scheduler_view_syncjobs_paging]
@fromRow INT,
@toRow INT
AS
BEGIN

WITH SyncJobs AS
(
      SELECT coalesce(instance.Schedule_Time,
scope.ScheduleTime,getdate()) as ScheduleTime
    ,scope.ScopeName
    ,instance.Status
      ,instance.Schedule_Time as starttedTime
      ,logs.logDetails
      ,ROW_NUMBER() OVER (ORDER BY scope.ScopeID DESC) AS 'RowNumber'
      FROM scheduler_scope scope
      LEFT JOIN scheduler_SyncInstance instance on
instance.ScopeId=scope.Scopeid
      LEFT JOIN scheduler_logs logs on
logs.InstanceID=instance.InstanceId
)
SELECT Count(*) as TotalCount
FROM SyncJobs;

WITH SyncJobs AS
(
      SELECT coalesce(instance.Schedule_Time,
scope.ScheduleTime,getdate()) as ScheduleTime
    ,scope.ScopeName
    ,instance.Status
      ,instance.Schedule_Time as starttedTime
      ,logs.logDetails
      ,ROW_NUMBER() OVER (ORDER BY scope.ScopeID DESC) AS 'RowNumber'
      FROM scheduler_scope scope
      LEFT JOIN scheduler_SyncInstance instance on
instance.ScopeId=scope.Scopeid
      LEFT JOIN scheduler_logs logs on
logs.InstanceID=instance.InstanceId
)
SELECT *
FROM SyncJobs
WHERE RowNumber BETWEEN @fromRow AND @toRow;

END

GO