Wednesday, 30 November 2011

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

AllData

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


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


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


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.