Different Pagination Methods in TSQL

In this tutorial, we see how to retrieve records from a particular table using pagination concept. In traditional SQL we sometimes send unrequired data to frontend UI even though it is not displaying the same. Most of the modern UIs and collaborative systems are interested only in qualitative and quantitative contextual data. So the idea behind pagination in TSQL is to send only the required data which frontend UI is anticipating rather than sending lot of unnecessary data. This increases performance and also scalability of SQL Server database.

IMPORTANT: The code in the following tutorial will be revised continuously in future for more performance (such as using Indexes etc.,)

Below code depicts two ways to get paginated data. First way is to use traditional CTE which uses ROW_NUMBER() with OVER clause having required ORDER BY clauses. And then run a select query on top of this CTE to get the results. Second approach is new in SQL Server 2012 which uses OFFSET and FETCH NEXT ROWS ONLY to get results. Second approach is far more readable and easy to understand, but for legacy SQL Server versions it is not available as it is included only in 2012 version.

The syntax of OFFSET – FETCH NEXT ROWS ONLY is quite simple, it simply conveys that do not include rows which are beyond and including OFFSET and then FETCH ONLY n ROWS which are NEXT to OFFSET, where n is the number we need to specify to tell how many number of rows are required. In the first CTE approach, we simple use a WHERE clause with BETWEEN to get the rows of interest.

For simplicity in code, I have divided both the methodologies into separate stored procedures and then executed them. Inputs for these stored procedures are PageNumber and PageSize.

Check out following code –

/******************** Use Adventure Works 2012 Database ********************/ 
 
USE [AdventureWorks2012]
GO
 

/****************** Create Pagination Stored procedure 1 *********************/
 
IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'P' AND name = 'GetRecentSalesOrdersMethod1')
BEGIN
    DROP PROCEDURE [dbo].[GetRecentSalesOrdersMethod1];
END
GO
 
CREATE PROCEDURE [dbo].[GetRecentSalesOrdersMethod1]
(
       @PageNumber [int],
       @PageSize [int]
)
AS
BEGIN
    SET NOCOUNT ON;

 /*************************** Method 1 using CTE *****************************
Using CTE, we can get the TOP rows of our interest. And then we can query on
that CTE to get the range of records of our interest. ROW_NUMBER() with OVER
ORDER BY can is used to version each row, we have to use this to get the range
because we cannot use SalesOrderDetailId which can be or may be started from
different numbers. So we use RowNumber to get the range in the select statement.
 ****************************************************************************/
 WITH PAGES AS 
 (
       SELECT TOP (@PageNumber * @PageSize)
              [SalesOrderDetailID],
              [CarrierTrackingNumber],
              ROW_NUMBER() OVER (ORDER BY [ModifiedDate] DESC, [SalesOrderDetailID] DESC) AS RowNumber
       FROM
              [Sales].[SalesOrderDetail]
 )
       SELECT 
              [SalesOrderDetailID],
              [CarrierTrackingNumber]
       FROM 
              PAGES 
       WHERE 
              RowNumber BETWEEN ((@PageNumber - 1) * (@PageSize)) + 1 AND (@PageNumber * @PageSize);

END
GO


/****************** Create Pagination Stored procedure 2 *********************/
 
IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'P' AND name = 'GetRecentSalesOrdersMethod2')
BEGIN
    DROP PROCEDURE [dbo].[GetRecentSalesOrdersMethod2];
END
GO
 
CREATE PROCEDURE [dbo].[GetRecentSalesOrdersMethod2]
(
       @PageNumber [int],
       @PageSize [int]
)
AS
BEGIN
    SET NOCOUNT ON;

 /*************************** Method 2 using OFFSET *****************************
IN SQL Server 2012, you can use OFFSET..FETCH to get the pagination. OFFSET specifies
the last record which needs to be ignored and from there the fetching starts.
We can specify FETCH NEXT with a number to get those many number of records from 
offset.
 *******************************************************************************/
        SELECT
                [SalesOrderDetailID],
                [CarrierTrackingNumber] 
        FROM 
                [Sales].[SalesOrderDetail] 
        ORDER BY 
                [ModifiedDate] DESC, [SalesOrderDetailID] DESC 
                OFFSET ((@PageNumber - 1) * (@PageSize)) ROWS FETCH NEXT @PageSize ROWS ONLY;

END
GO
 
/************************ Execute Stored Procedcure **************************/
EXEC [dbo].[GetRecentSalesOrdersMethod1] 99, 10;
EXEC [dbo].[GetRecentSalesOrdersMethod1] 99, 10;
GO

 

Output –

Pagination

You may also like...