Pagination code best practices SQL Server

Pagination Strategy

Pagination is one of the very common need to design a web page (Using C#, PHP, JQuery etc). While writing the code for pagination, some developers think, it is a bad idea to call database multiple times to load data for each web pages instead of that 
load all the data at the same time and store in application cache. This strategy is called application level pagination. There are many types of grid which support inbuilt application level pagination. This strategy work good for if we are dealing with less volume of data. For example if data to be loaded from a table named tblUser, below script has to execute to implement application level pagination:

SELECT * FROM tblUser

SELECT @@ROWCOUNT  

Total logical read: 319

Note: Let's assume tblUser has 100000 records.

Let us assume, if that result set of the query has 100k records or 1 million records or even more than this. Is it a good idea to load all those data at the same time during the first load of a web page?  In general an end user is never interested in more than two to three pages. What is need to load all records of a table?   

In this strategy a web page with pagination loads fast with if data volume is very less, as soon as data volume will increase the same web page will take time from seconds to minutes to hours to load it. It may come to a situation that web page is not loading at all.     We should avoid application level pagination if you predicate data volume may increase with time.

Best strategy is, load only those data which is necessary. During the first load of a web page, fetch only those data from database which is needed to show only on that web page. When an end user click on any other page then fetch only those data from database which is needed to show in that web page. This strategy is called database level pagination. This approach is some extent independent from volume of data in the database. After implementing this, a web page which was initially taking long time to load can load in within fraction of a second. 

Sample script to implement   database level pagination:

CREATE PROCEDURE GetUsers(

       @ntPageNumber AS INT,

       @ntPageSize AS INT,

       @ntTotalRecord AS INT OUTPUT

)

AS

BEGIN

      

       SET NOCOUNT ON

       SELECT @ntTotalRecord = COUNT(*) FROM tblUser

       /* Alternative & efficient way to get total records count

       SELECT @ntTotalRecord = SUM(st.row_count)

       FROM sys.dm_db_partition_stats st

       WHERE object_name([object_id]) = 'tblUser'

              AND (index_id < 2)

       */

       ;WITH cteLoadUser

       AS (

               SELECT *

                       ,ROW_NUMBER() OVER (

                             ORDER BY ntUserID

                             ) AS ntRowID

               FROM tblUser

               )

       SELECT *

       FROM cteLoadUser

       WHERE ntRowID BETWEEN (@ntPageNumber - 1) * @ntPageSize + 1

          AND @ntPageNumber * @ntPageSize

       /* From SQL Server 2012

       ;WITH cteLoadUser

       AS (

               SELECT *

                       ,ROW_NUMBER() OVER (

                             ORDER BY ntUserID

                             ) AS ntRowID

               FROM tblUser

               )

       SELECT *

       FROM cteLoadUser

       ORDER BY ntUserID

       OFFSET @ntPageSize * (@ntPageNumber - 1) ROWS

       FETCH NEXT @ntPageSize ROWS ONLY; */

END

Total logical read: 3 + 5 = 8

Comparison of execution plan(Application level vs Database level pagination):

Pagination best practices SQL SERVER

Here we can notice in case of large volume of data, database level pagination is much better than application level pagination.