Web developers wiki ASP.NET Sitecore Sharepoint Kentico by Evident Interactive

When working with a large number of rows one often wants to return data in batches (a.k.a. pages) instead of returning all rows at once. In SQL Server 2012 this can be achieved by using the new offset-fetch clause, which is a part of the order-by clause. This article contains a brief description of the offset-fetch clause. The examples in this article use the following table and data:

create table person
(
	firstname nvarchar(16),
	lastname nvarchar(16),
	address nvarchar(16),
	city nvarchar(16)
	
)

insert into person values ( 'Bill' , 'Doe' , 'Amberroad 12' , 'Washington D.C.' )
insert into person values ( 'John' , 'Doe' , 'Main street 15' , 'Denver' )
insert into person values ( 'Mary' , 'Smith' , 'Broadway av 4' , 'New York' )
insert into person values ( 'Mary' , 'Jones' , 'Rodeo drive 10' , 'Beverly Hills' )
insert into person values ( 'Julia' , 'McAndrew' , 'Sunset blvrd 11' , 'Miami' )

Here is the basic syntax of the offset-fetch clause:

ORDER BY <order_by_list>
[ 
   OFFSET <offset_value> ROWS
   [
      FETCH NEXT <fetch_value> ROWS ONLY
   ]
]

This means that:
- The offset-fetch clause is a part of the order-by clause. It cannot be used without an order-by clause.
- The "fetch next" is optional.
- The <offset_value> specifies how many rows are skipped (i.e. the output starts at row <offset_value> + 1).
- The <fetch_value> specifies the maximum number of rows to be returned.

Here are some examples.

A simple order-by without offset-fetch clause:

select * from person order by firstname

firstname        lastname         address          city
---------------- ---------------- ---------------- ----------------
Bill             Doe              Amberroad 12     Washington D.C.
John             Doe              Main street 15   Denver
Julia            McAndrew         Sunset blvrd 11  Miami
Mary             Smith            Broadway av 4    New York
Mary             Jones            Rodeo drive 10   Beverly Hills

(5 row(s) affected)

The following example skips the first row (i.e. the output starts at row 2) and returns all remaining rows:

select * from person order by firstname offset 1 rows

firstname        lastname         address          city
---------------- ---------------- ---------------- ----------------
John             Doe              Main street 15   Denver
Julia            McAndrew         Sunset blvrd 11  Miami
Mary             Smith            Broadway av 4    New York
Mary             Jones            Rodeo drive 10   Beverly Hills

(4 row(s) affected)

The following example skips the first row (i.e. the output starts at row 2) and returns 2 rows:

select * from person order by firstname offset 1 rows fetch next 2 rows only

firstname        lastname         address          city
---------------- ---------------- ---------------- ----------------
John             Doe              Main street 15   Denver
Julia            McAndrew         Sunset blvrd 11  Miami

(2 row(s) affected)

When the <offset_value> is higher than the number of rows, an empty resultset is returned:

select * from person order by firstname offset 10 rows

firstname        lastname         address          city
---------------- ---------------- ---------------- ----------------

(0 row(s) affected)

When the <fetch_value> is higher than the number of remaining rows, the resultset only contains the remaining rows:

select * from person order by firstname offset 1 rows fetch next 10 rows only

firstname        lastname         address          city
---------------- ---------------- ---------------- ----------------
John             Doe              Main street 15   Denver
Julia            McAndrew         Sunset blvrd 11  Miami
Mary             Jones            Rodeo drive 10   Beverly Hills
Mary             Smith            Broadway av 4    New York

(4 row(s) affected)

When using "fetch next" the offset clause cannot be omitted. So the following query is invalid and will return an error:

select * from person order by firstname fetch next 2 rows only

Msg 153, Level 15, State 2, Line 3
Invalid usage of the option next in the FETCH statement.

If you want the resultset to start at the first row, use “offset 0 rows”:

select * from person order by firstname offset 0 rows fetch next 2 rows only

firstname        lastname         address          city
---------------- ---------------- ---------------- ----------------
Bill             Doe              Amberroad 12     Washington D.C.
John             Doe              Main street 15   Denver

(2 row(s) affected)

The <offset_value> and <fetch_value> can also be specified using variables:

declare @offset int = 1
declare @numberofrows int = 2

select * from person order by firstname offset @offset rows fetch next @numberofrows rows only

firstname        lastname         address          city
---------------- ---------------- ---------------- ----------------
John             Doe              Main street 15   Denver
Julia            McAndrew         Sunset blvrd 11  Miami

(2 row(s) affected)

According to the SQL Server documentation the word “rows” can be replaced by "row" and "next" can be replaced by "first". This means that the following queries are identical and will return the same resultset:

select * from person order by firstname offset 0 rows fetch next 2 rows only

select * from person order by firstname offset 0 row fetch next 2 rows only

select * from person order by firstname offset 0 rows fetch next 2 row only

select * from person order by firstname offset 0 rows fetch first 2 rows only

 © Evident Interactive BV