How to use OFFSET / FETCH option in SQL Server Order By Clause ?

Posted by DBATAG on December 10, 2010 Leave a comment (1) Go to comments

What is OFFSET FETCH options in SQL Server ?

SQL Server Denali (2011) has enhanced the ORDER BY clause. Now with ORDER BY clause we can also specify the OFFSET and FETCH options. By using OFFSET and FETCH I can specify, I wanted to have 6 records in result sets and those 6 records should start from 40th record. OFFSET/FETCH are designed mainly for ad-hoc paging purpose.

Lets understand this quickly with an Example with a Sample Table, for Demonstration

-- Creating a Sample Table which has only one column as ID
create table paging_test
(
ID int
)
go
--- Inserting 100 records in that table in one go using While Loop
declare @i int = 1
while (@i<100)
begin
    insert    paging_test values (@i)
    set @i += 1
end

Example: SELECT QUERY using ORDER BY clause including OFFSET & FETCH

--- Seleting 6 rows starting from 40th record
select * from paging_test
order by ID
Offset 40 rows
fetch next 6 rows only

OUTPUT

In the above query, we mentioned we wanted to select records from (40) and we wanted to select only 6 (six) record from that start point.

OFFSET: Specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero.

FETCH: Specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that is greater than or equal to one. Taken from Books Online of Denali

NOTES

Pagination for programmers is now very easy, previously we need to use ROW_NUMBER(), TOP, Order BY clauses to get similar output
Using OFFSET and FETCH is bit faster then using ROW_NUMBER(), TOP, Order BY clauses
Very Simple to use for Ad-Hoc paging
ROW and ROWS are synonyms and are provided for ANSI compatibility.
FETCH can be used with either FIRST or NEXT like
- FETCH NEXT 6 ROWS ONLY or FETCH FIRST 6 ROWS ONLY anything can be used
- OFFSET (40) ROWS or OFFSET (40) ROW or Offset 40 rows anything can be used

Tags : What is OFFSET FETCH in SQL Server,OFFSET FETCH options in ORDER by, SQL Server order by enhancement,order by enhancement example, sql server 2011 order by demo,sql server 2011 offset fetch example, sql server denali fetch offset demo