When we have huge result set it doesn’t make sense to return the complete result set to the application and leave it to the datagrid, gridview or any other data control to handle the data paging. Because it wastes lot of network resources, puts pressure on the database server and the application server, so it makes page loading very slow. In such scenario it makes sense to return only the rows which needs to displayed on the current page, when user clicks next page/next result set then bring the next set of rows. In this way it reduces the unnecessary network traffic and load on the data base server, which in-turn helps in faster loading of the page.

With Sql Server 2012 Microsoft has introduced OFFSET and FETCH feature for data paging. Prior to Sql Server 2012 CTE was on of the option for data paging.

To demonstrate data paging let us create a customers table as shown in the below image by the following script:

Script:

--Create Demo database
CREATE DATABASE SqlHintsCTEDataPaging
GO
USE SqlHintsCTEDataPaging
GO
-- Create an Customers table.
CREATE TABLE dbo.Customers
( Id INT  PRIMARY KEY, FirstName NVARCHAR(50),
  LastName NVARCHAR(50))
-- Populate Customer table with sample data
INSERT INTO dbo.Customers VALUES
 (1, 'Basavaraj', 'Biradar') ,(2, 'Abhishiek', 'Akkanna')
,(3, 'Santosh', 'Patil'), (4, 'Sachin', 'Tendulkar')
,(5, 'Virendra', 'Shewag'),(6, 'Virat', 'Kohli')
,(7, 'Ajinkya', 'Rahane'), (8,'Cheteshwar', 'Pujara')

Data Paging by OFFSET/FETCH

We can write a stored procedure like below for Data Paging by using the OFFSET..FETCH pagination feature introduced in Sql Server 2012. This stored procedure gives specified number of rows (i.e. it can be specified by the parameter @pageSize) for the passed page number (i.e. parameter @pageNum) sorted by the FirstName column.

CREATE PROCEDURE dbo.GetCustomersPagedDatabyFetch
(
  @pageNum INT,
  @pageSize INT
)
AS
BEGIN
	SELECT Id, FirstName, LastName
	FROM dbo.Customers WITH(NOLOCK)
	ORDER BY FirstName
	OFFSET (@pageNum - 1) * @pageSize ROWS
	FETCH NEXT @pageSize ROWS ONLY  
END

Try to get the first page data sorted by FirstName by executing the above Stored Procedure, assume that the page size as 3 for our example.

EXEC dbo.GetCustomersPagedDatabyFetch 
         @pageNum = 1, @pageSize = 3

RESULT:

Now try to get the second page data sorted by FirstName by executing the below statement.

EXEC dbo.GetCustomersPagedDatabyFetch 
         @pageNum = 2, @pageSize = 3

RESULT:

Now try to get the third page data sorted by FirstName by executing the below statement.

EXEC dbo.GetCustomersPagedDatabyFetch 
         @pageNum = 3, @pageSize = 3

RESULT:

The reason we got only two records in the above result even when the data page size 3 is, the demo Customers table created in this article has only 8 records. So, with page size as 3 the third page will have only two records.

Data paging by CTE

We can write a stored procedure like below for Data Paging by CTE. This stored procedure gives specified number of rows (i.e. it can be specified by the parameter @pageSize) for the passed page number (i.e. parameter @pageNum) sorted by the FirstName column.

CREATE PROCEDURE dbo.GetCustomersPagedData
(
	@pageNum INT,  -- Data page number
	@pageSize INT  -- Number of rows per page 
)
AS
BEGIN
	WITH PagingCTE AS
	(
	 SELECT Id, FirstName, LastName, 
          ROW_NUMBER() OVER (ORDER BY FirstName) AS RowNumber
	 FROM dbo.Customers WITH(NOLOCK)
	)
	SELECT *
	FROM PagingCTE
	WHERE RowNumber BETWEEN (@pageNum - 1) * @pageSize + 1 
              AND @pageNum * @pageSize	
END

Try to get the first page data sorted by FirstName by executing the above Stored Procedure, assume that the page size as 3 for our example.

EXEC dbo.GetCustomersPagedData @pageNum = 1, @pageSize = 3

RESULT:

Now try to get the second page data sorted by FirstName by executing the below statement.

EXEC dbo.GetCustomersPagedData @pageNum = 2, @pageSize = 3

RESULT:

Now try to get the third page data sorted by FirstName by executing the below statement.

EXEC dbo.GetCustomersPagedData @pageNum = 3, @pageSize = 3

RESULT:

The reason we got only two records in the above result even when the data page size 3 is, the demo Customers table created in this article has only 8 records. So, with page size as 3 the third page will have only two records.

Data paging by CTE with dynamic Sort column

In the previous example of this article the SP GetCustomersPagedData was supporting the data paging but the sort column here was always the FirstName column. But in real world we will always not have the data paging with sorting by just one column. For example, for the customers table we may need to support data paging with either FirstName or LastName as the sorting column. We can create a SP like below which supports the data paging by the FirstName or LastName as the sort column by using CTE:

CREATE PROCEDURE dbo.GetCustomersDynamicSortColumn
(
	@pageNum INT,
	@pageSize INT,
	@sortColumnName VARCHAR(50)
)
AS
BEGIN
  WITH PagingCTE AS
  (
    SELECT Id, FirstName, LastName, ROW_NUMBER() OVER 
     (ORDER BY CASE 
       WHEN @sortColumnName = 'FirstName' THEN  FirstName 
       WHEN @sortColumnName = 'LastName' THEN  LastName 
						 END) AS RowNumber
    FROM dbo.Customers WITH(NOLOCK)
  )
  SELECT *
  FROM PagingCTE
  WHERE RowNumber BETWEEN (@pageNum - 1) * @pageSize + 1 
   AND @pageNum * @pageSize
END

Now try to execute the above stored procedure to get the first page data with page size as 3 and sort column as FirstName:

EXEC dbo.GetCustomersDynamicSortColumn 
      @pageNum = 1, @pageSize = 3, @sortColumnName = 'FirstName'

RESULT:

Now execute the following statement to get the first page data with page size as 3 and sort column as LastName:

EXEC dbo.GetCustomersDynamicSortColumn 
      @pageNum = 1, @pageSize = 3, @sortColumnName = 'LastName'

RESULT: