SQL Server 2012: OFFSET-FETCH

This is the first in a number of articles I intend to write whilst studying for my MCSE, so that I can not only consolidate the things I’ve learned, but also to share stuff with you guys.

The OFFSET-FETCH clause was new to SQL Server 2012 and provides a different means of filtering data. OFFSET-FETCH can provide some similar functionality to the TOP clause, but it has some particularly useful applications in your T-SQL queries.

The syntax looks something like this:-

OFFSET x ROW/ROWS FETCH FIRST/NEXT x ROW/ROWS ONLY

It might look something like this in practice:-

OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY

So, what does it do?  Well it actually has two parts.

  • The first part is OFFSET x ROW/ROWS.  This part ‘skips’ the first x rows that the query would normally return, and then returns the rest.  This part can be used by itself, without using FETCH.
  • The second part is FETCH FIRST/NEXT x ROW/ROWS.  This part returns the next x rows after the OFFSET portion of the clause.  This can only be used if OFFSET is in use.

So, for our example above (OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY) the return would be the first 10 rows.

Before I go into a couple of other examples, there are a few things worth pointing out:-

  1. The OFFSET-FETCH clause requires ORDER BY to work, and must follow ORDER BY (in fact,some people see it as a part of ORDER BY).  Without ORDER BY, it won’t work.
  2. You can use either the word ‘ROW’ or ‘ROWS’ in either part of the clause with no difference in output.  They are completely interchangeable and it’s literally down to you how you want to use them – I like to switch them around to aid readability of the query.
  3. Similarly, you can use FIRST or NEXT in the second part of the clause and it doesn’t make any difference to the query output – again use whichever aids readability.
  4. Unlike TOP(x) there is no functionality for you to return a percentage of rows.
  5. You can’t use TOP(x) and OFFSET-FETCH at the same time!  Use one or the other.

To top it off lets look at a couple of examples using the AdventureWorks2012 database.  For each I’ll provide an example and an explanation of how the OFFSET-FETCH clause will affect the query output,

Example 1

SELECT [AddressLine1]
 ,[AddressLine2]
 ,[City]
FROM [AdventureWorks2012].[Person].[Address]
ORDER BY AddressLine1
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY

This example skips the first 10 rows, and then returns the next 20.  For the following examples the query should be the same, the only part that will change is the last line containing the OFFSET-FETCH clause.

Example 2

...rest of query...
OFFSET 30 ROWS

This example would exclude the first 30 rows of the query.  If without this your query would normally return 90 rows, you would get the last 60 by using this.

Example 3

...rest of query...
OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY

This would return only the first row of the query

Example 4

...rest of query...
OFFSET 0 ROW FETCH NEXT 1 ROWS ONLY

Whilst some of the text might look different, this would give you exactly the same result as example 3.  Remember, the word ROW can be replaced with ROWS and the keyword NEXT and FIRST are also interchangeable.

One of the most useful applications of this clause is to provide paging for your data.  For example, you might want page one of a report to show records from 1 to 50:-

OFFSET 0 ROWS FETCH FIRST 50 ROWS ONLY

and then in page 2, the next 50 rows:-

OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY

And so on…quite useful, isn’t it?

 

One comment

  • December 18, 2015 - 9:30 pm | Permalink

    actually i m doing this in a query only see this whole logicDeclareCursor agg isselect Agg_no,client.Client_name clenitn,End_date from AGGREMENT,client WHERE client.client_id = aggrement.client_id;Beginfor agrmt in agg loop :P2_END_DATE := agrmt.End_date; :P2_AGG_NO := agrmt.Agg_no; :P2_CLIENT_NAME := agrmt.clenitn; if to_date(agrmt.End_date,’dd-mon-rr’) <= to_date(SYSDATE,'dd-mon-rr') THEN htp.p('open script); htp.p('pop(''' || :P2_AGG_NO || ''',''' || :P2_END_DATE || ''',''' || :P2_CLIENT_NAME || ''');'); htp.p('if(v == true){'); update aggrement set checked='y' where agg_no = :P2_AGG_NO; htp.p('}else{'); update aggrement set checked='n' where agg_no = :P2_AGG_NO; htp.p('}close script'); end if;end loop;End;This is my whole logic where I am calling a javascript function from select query from that function I m getting the value of 'v' which is a boolean variable and after checking this variable's value I want to update my table accordingly.this is the Pop function:var v;function pop(ag_no,end_dt,clnt_nm){v = confirm("Aggrement: " + ag_no + " with Client: " + clnt_nm + " is going to end on " + end_dt + " Date.");}For this either I need to run the update table qurery under javascript or I can reffer the javascript variable 'v' into my pl/sql function.

  • Leave a Reply

    Your email address will not be published. Required fields are marked *