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:

Migrationa

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:

Migrationa

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

Migrationa

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

Migrationa

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

Old technique:

Migrationa

New technique:

Migrationa

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.

7 thoughts on “Pagination in SQL Server 2012 and above

Add yours

  1. First of all, a very good article like always.
    Also, I would like to know about how can we get the runtime stats using extended events and what are the different approaches to get those??

    Like

    1. Thanks Sumit. Yes there are different ways to capture the performance stats of a stored procedure. Using Extended event and profiler are the key ones. I will post a blog to cover this subject soon.

      Like

  2. Superb, this is very useful for web developer as well to improvise user experience on the web page. Right paging at DB level simplify front end design.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

Up ↑

%d bloggers like this: