Using OFFSET and FETCH

 

Introduction

Many times while developing our applications we feel the need of pagination, where our User Interface (UI) has to list a number of records and fetching them all at once and listing is not a feasible option because of the following reasons -

1. High utilization of the network bandwidth which if on a higher side might even choke up the bandwidth.
2. User is not guaranteed to see the latest details in a multi user environment.
3. High need of RAM on local machine for caching/processing.

So, the solution which is generally implemented in this situation was to fetch only the relevant records from the backend. Until Denali the following were the options used to counter this situation -

1. Before SQL 2005 – ORDER BY clause in combination with TOP
2. From SQL 2005 onwards – ROW_NUMBER() function with a WHERE clause

And from Denali, we can use ORDER BY clause in combination with OFFSET and FETCH

Implementation

Let’s see how we can get the same output using all of the 3 ways explained above and try to fetch records from 3 to 4 assuming page size to be 2.

Total Records are as under

1. ORDER BY + TOP

DECLARE @PageNo AS INT

DECLARE @PageSize AS INT

SET @PageNo = 2

SET @PageSize = 2

SELECT

  * 

FROM (SELECT

        TOP (@PageSize) *

      FROM (SELECT 

              TOP (@PageNo * @PageSize) *

            FROM 

              dbo.DemoTable DT

            ORDER BY

              ID ASC) X

      ORDER BY

        X.ID DESC) Y

ORDER BY

  Y.ID ASC

Output

 

2. ROW_NUMBER() + WHERE

DECLARE @PageNo AS INT

DECLARE @PageSize AS INT

SET @PageNo = 2

SET @PageSize = 2

;WITH Data AS (  

SELECT

  *,

  ROW_NUMBER()OVER(ORDER BY DT.ID ASC) Rno

FROM

  dbo.DemoTable DT

)

SELECT 

  ID,NAME,CITY

FROM

  Data

WHERE

  Rno BETWEEN ((@PageNo - 1) * @PageSize) + 1 AND ((@PageNo - 1) * @PageSize) + @PageSize

Output

 

3. FETCH + OFFSET

DECLARE @PageNo AS INT

DECLARE @PageSize AS INT

SET @PageNo = 2

SET @PageSize = 2

SELECT 

  *

FROM 

  dbo.DemoTable DT

ORDER BY 

  DT.ID

OFFSET ((@PageNo - 1) * @PageSize)) ROWS

FETCH NEXT @PageSize ROWS ONLY

Output

Performance

I did a small test using all the 3 ways and have found the Denali (OFFSET and FETCH) way the best performing one followed by the ROW_NUMBER().

 Conclusion

I would prefer using the Denali way just for 2 simple reasons -

• Simplicity of code
  
• Better performance

Remarks

1. The Denali code is based on SQL Server Denali CTP 1 and might change after further releases.