Sql Server : Retrieve the next ‘n’ records after ‘m’ using OFFSET & FETCH.
- Posted by Sqltimes
- On December 17, 2014
- 0 Comments
Question: Paging through records
Quite often, when paging through a large set of records, we display a set of records at a time on user interface. Upon clicking next, the next set of records are displayed. In short, this type of paging could be achieved using CTE and ROW_NUMBER(). Every iteration we change the ‘@from’ and ‘@to’ variables to bring the next set of records.
See example below.
Paging using CTE
Old Approach
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
---- Using CTE and ROW_NUMBER()--; WITH SV_RowNumberAS( SELECT StoredValueID , LocalID , ExpireDate , ROW_NUMBER() OVER (ORDER BY StoredValueID ASC) AS RowN FROM dbo.StoredValue)SELECT *FROM SV_RowNumberWHERE RowN BETWEEN 25 AND 34ORDER BY StoredValueID ASCGO |
New Approach
Starting Sql Server 2012, there is an easier way using OFFSET and FETCH to ORDER BY clause. With these, you can retrieve the next set of ‘n’ records after skipping ‘m’ records. See example below:
|
1
2
3
4
5
6
7
8
9
|
---- Using OFFSET & FETCH--SELECT StoredValueID , LocalID , ExpireDateFROM dbo.StoredValueORDER BY StoredValueID ASC OFFSET 24 ROWS FETCH NEXT 10 ROWS ONLYGO |
Using OFFSET FETCH
Points to keep in mind:
- In general both approaches are efficient.
- In many cases, the query cost with CTE is same as using OFFSET/FETCH (with some exceptions)
- In some cases, using OFFSET/FETCH is many times more efficient than CTE
OFFSET FETCH Query Cost Comparision
- Using OFFSET/FETCH makes the code more intuitive
- OFFSET/FETCH could only be used in ORDER BY clause
- OFFSET clause is required for FETCH clause.
- TOP cannot be used in the same query as OFFSET. If you do, you’ll see the following error.
Msg 10741, Level 15, State 2, Line 5 A TOP can not be used in the same query or sub-query as a OFFSET.
Hope this helps,
_Sqltimes
_Sqltimes
0 Comments