http://www.sqlservercentral.com/blogs/juggling_with_sql/2011/11/30/using-offset-and-fetch/

Printed 2014/08/28 07:18AM

Using OFFSET and FETCH

By vinaypugalia, 2011/11/30

 

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 -

Remarks

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

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.