Pagination in SQL Server 2012 and above

Prior to SQL Server 2012, implementing pagination with millions of records was a big challenge for any developer. A commonly used workaround to implement pagination in SQL Server  was the ROW_NUMBER() function. The ROW_NUMBER function, like many of its siblings (i.e. RANK etc.), is a window function that returns a sequential integer value per row of a given partition of result set.

There was a new pagination feature introduced in SQL Server 2012 using OFFSET-FETCH filter. The standardized clause works by limiting the result set in a given SQL query. This  feature also appears to be less expensive from the performance viewpoint than the previous technique we were using.

Although this new pagination technique is very simple and easy to implement, people are still using the old technique which may cause performance bottleneck and can increase response time. Please refer MSDN for more details.

In this blog post, I will show you a quick demo to compare both the pagination techniques:

Pagination Technique prior to SQL Server 2012

In most cases, we were using ROW_NUMBER() ranking function to implement pagination before SQL Server 2012. It was working fine functionally but was not optimal due to index scans and sort operations.

Following T-SQL will implement pagination using ROW_NUMBER(). @pageNumber defines the page index and you can limit the page size with setting up the value of @pageSize.

CREATE PROCEDURE [dbo].[ReadDemo_WithOldPaginationMethod]
    @pageNumber INT ,
    @PageSize INT
AS
    BEGIN
	
        SELECT  SalesOrderID ,
                SalesOrderDetailID ,
                CarrierTrackingNumber ,
                OrderQty ,
                ProductID ,
                SpecialOfferID ,
                UnitPrice ,
                UnitPriceDiscount ,
                LineTotal ,
                rowguid ,
                ModifiedDate
        FROM    ( SELECT    SalesOrderID ,
                            SalesOrderDetailID ,
                            CarrierTrackingNumber ,
                            OrderQty ,
                            ProductID ,
                            SpecialOfferID ,
                            UnitPrice ,
                            UnitPriceDiscount ,
                            LineTotal ,
                            rowguid ,
                            ModifiedDate ,
                            RowNum = ROW_NUMBER() OVER ( ORDER BY SalesOrderID )
                  FROM      Sales.SalesOrderDetail
                ) SalesOrderDetail
        WHERE   ( SalesOrderDetail.RowNum >= ( ( @pageNumber - 1 ) * @PageSize
                                               + 1 )
                  AND SalesOrderDetail.RowNum <= @pageNumber * @PageSize
                )
        ORDER BY SalesOrderID ASC
    END

It works perfectly fine functionally. Please see output as below:

Pagination Technique from SQL Server 2012

In the SQL Server 2012 a new feature was added in the ORDER BY clause, to query optimization of a set data, making work easier with data paging for anyone who writes in T-SQL as well for the entire Execution Plan in SQL Server.

Syntax

[ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n][] ] 

 ::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }    [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]}

Arguments

• OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
  Specifies the number of rows to skip, before starting to return rows from the query expression. The argument for the OFFSET clause can be an integer or expression that is greater than or equal to zero. You can use ROW and ROWS interchangeably.
• FETCH { FIRST|NEXT } <rowcount expression> { ROW|ROWS } ONLY
  Specifies the number of rows to return, after processing the OFFSET clause. The argument for the FETCH clause can be an integer or expression that is greater than or equal to one. You can use ROW and ROWS interchangeably. Similarly, FIRST and NEXT can be used interchangeably.

Limitations with this feature

• ORDER BY is mandatory to use OFFSET and FETCH clause.
• OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
• TOP cannot be combined with OFFSET and FETCH in the same query expression.
• The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.

Please see below the T-SQL script to implement pagination with new technique.

CREATE PROCEDURE [dbo].[ReadDemo_WithNewPaginationMethod]
    @pageNumber INT ,
    @PageSize INT
AS
    BEGIN
        SELECT  SalesOrderID ,
                SalesOrderDetailID ,
                CarrierTrackingNumber ,
                OrderQty ,
                ProductID ,
                SpecialOfferID ,
                UnitPrice ,
                UnitPriceDiscount ,
                LineTotal ,
                rowguid ,
                ModifiedDate
        FROM    Sales.SalesOrderDetail
        ORDER BY SalesOrderID ASC
                OFFSET ( @pageNumber - 1 ) * @PageSize ROWS  
			FETCH NEXT @PageSize ROWS ONLY  
    END

This new stored procedure will also give same output:

Now, lets review execution plan of both the techniques. We can see old technique is three times expensive than new technique.

Let’s deep dive in the plan using SentryOne Plan Explorer

Estimated subtree cost of the old technique is much higher than new technique

Old technique:

New technique:

In this demo, we saw both the options and comparison from performance perspective. The processing time reduced in new technique.

We can see that the pagination of a SELECT statement is simple to set up and can be considered an excellent resource for large amounts of data.

Please leave your comment for any query or feedback.

Cheers.