Introduction
This article gives a simple tutorial of implementing custom paging on the SQL Server side. Here we will see how paging techniques have been evolving since Microsoft SQL Server 2000.

This article is intended to answer the following questions:
Once we understand the advantage of custom paging over default paging, we will go for wiring it up with ASP.Net and the C# language. So I am limiting the scope of this article to understanding the custom paging techniques.

Setting the environment to be implemented


To implement paging in SQL Server 2012 and later versions (to have a hand on exposure), I assume you already have a running version of it. If not, you can download the express edition free of cost from Microsoft's website here.

Overview


Huge data with paging has been a big headache for the developer. There is a default paging option available with some data controls. These controls do manage paging automatically without writing so much code but provide poor performance. This default paging is handled at the page level (in the .Net environment) and consumes memory. Few known techniques have been impaired with these controls to enhance the performance. But they still give bad performance over custom-paging.

In general, to handle a Page index change, the following mechanism/tricks are applied:
  1. Get all records on every call from the database and directly assign it to the DataControl. This is commonly known as Default-paging.
  2. Get all the records for the first time only and store them into some state variables like ViewState/memory etc.
  3. Fetch all records for the first time only and cache them. For future calls, the data/record is fetched from the cache instead of the database. This technique is also used with DataSource controls like sqlDataSource etc.
Custom Paging In SQL Server

We will analyze the first option. In this, all records are fetched for just showing a single page record to the user. Other page records are not shown, then why, fetch all records from the database on each page index change? It is going to take CPU time as well as IO read for fetching all records. It provides a very bad performance.

In the second practice, a huge quantity of records/data is saved in memory that hampers the performance in terms of a limited amount of memory for many users.

In the third approach, where the cache is implemented is just minimizing the database hits. All other problems still exist there. This also requires memory to keep/maintain the dataset cache and cause problems. Secondly, it serves stale data/records. Microsoft clarifies it as "However, you should not cache objects that hold resources or that maintain state that cannot be shared among multiple requests, such as an open DataReader object."

So programmers started looking for better approaches/techniques and zeroed on the database level. This custom paging approach fetches only page-size records for a particular page. It prevents excessive reads for the database engine and saves CPU time. The DataControl requires counting the total-records so that they can show page numbers and this one is also queried along with query.

In the earlier version of Microsoft SQL, there were no customized clauses or functions for paging. So many programmers tried many approaches at the database level to implement the requirements. Doing some hacks/tricks (use of a temp table etc) in earlier versions like Microsoft SQL Server 2000 was much better than implementing default paging or using cache/state variables.

So, let us start with the Microsoft SQL Server 2012 edition.

Microsoft SQL Server 2012 edition


In this new version/edition, we have a new clause "OFFSET FETCH Next" that extends the existing ORDER BY clause. This is something like a ready-made option for paging. Before this, there was no direct function/clause to implement custom paging. OFFSET specifies the number of rows to skip and FETCH specifies the number of rows to return (after skipping rows provided by the OFFSET clause). The following syntax is taken from Microsoft's MSDN for a better view of it:
  1. OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
  2. FETCH { FIRST|NEXT } <rowcount expression> { ROW|ROWS } ONLY
Custom Paging In SQL Server

Implementing custom paging in Microsoft SQL Server 2012 is very easy compared to earlier versions. It reads only the required number of rows and thus saves the IO as well as CPU time that occurs from reading excess rows. It works like the TOP clause with Order By. So it gives better performance than the temp table mechanism.

A Simple usage syntax is like:
  1. CREATE PROCEDURE dbo.uspGetPageRecords
  2. (
  3. @OffSetRowNo INT,
  4. @FetchRowNo INT
  5. )
  6. AS
  8. SELECT colName1, colName2, colName3, colName4 FROM tblMyTableName
  9. ORDER BY colNameForSorting
  10. OFFSET ( @OffSetRowNo-1 ) * @FetchRowNo ROWS
  11. FETCH NEXT @FetchRowNo ROWS ONLY
  13. GO
A simple example is:
  1. CREATE PROCEDURE dbo.uspGetPageRecords
  2. (
  3. @PageNo INT,
  4. @RecordsPerPage INT
  5. )
  6. AS
  7. SET NOCOUNT ON;
  8. --The offset specified in a OFFSET clause may not be negative.
  9. --So check & set the initial for avoiding negative OFFSET
  10. IF @PageNo < 1
  11. SET @PageNo = 1
  13. SELECT AutoID, Name, City, RegistrationDate FROM tblUserMaster
  14. ORDER BY RegistrationDate
  15. OFFSET ( @PageNo - 1 ) * @RecordsPerPage ROWS
  16. FETCH NEXT @RecordsPerPage ROWS ONLY
  18. GO
The following are the limitations of using Offset Fetch,
  1. Fetch Next can't be used standalone, it requires Offset
  2. Offset can't be used standalone, it requires an order
  3. Top can't be combined with offset fetch next in the same query expression

Microsoft SQL Server 2008/2005 edition


Microsoft had introduced a new function ROW_NUMBER() with Microsoft SQL 2005/2008. In addition to this ROW_NUMBER(), two other approaches, Common Table Expression (CTE) and Derived Table concepts, were given.

The following is an approach with the help of CTE and ROW_NUMBER(),
  1. WITH ctePageRecord AS
  2. (
  3. SELECT AutoID, Name, City, RegistrationDate,
  4. , ROW_NUMBER() OVER (ORDER BY RegistrationDate ) AS rowNumber
  5. FROM tblUserMaster
  6. )
  7. SELECT AutoID, Name, City, RegistrationDate,
  8. FROM ctePageRecord
  9. WHERE rowNumber > 0 AND rowNumber <= 10
And if you don't want to hit your database twice for the Total record count or you don't want a second select/read then the following approach would be the better option. If you are using an Object/DTO then you can assign this total record count and do the stuff. In this approach, two sequence numbers are created with ROW_NUMBER(). The second one is ordered in reverse of the first one and due to this at each row level the total no of records = the sum of these two sequence fields - 1. So if you create your DTO, you just need to have one additional property/variable for holding this record total.
  1. WITH ctePageRecord
  2. AS
  3. (
  4. SELECT AutoID, Name, City, RegistrationDate,
  5. ROW_NUMBER() OVER(ORDER BY RegistrationDate) AS rowNumber ,
  6. ROW_NUMBER() OVER(ORDER BY RegistrationDate DESC) AS totalRows
  7. FROM tblUserMaster
  8. )
  9. SELECT AutoID, Name, City, totalRows + rowNumber -1 AS TotalRecords
  10. FROM ctePageRecord
  11. WHERE rowNumber BETWEEN 1 AND 10
  12. ORDER BY rowNumber

Wrap up


For a small number of records, one can use the default GridView paging option but with huge records, I would like to suggest the use of custom-paging at the SQL Server side.

To finish this tutorial, we will summarize: