OFFSET FETCH Clause In SQL Server 2012

OFFSET FETCH Clasue gives us an option to select the range of records from table.

This feature is very useful in implementing the paging in front end applications.

Syntax:

ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n][<offset_fetch>]

OFFSET : Specifies the number of row to skip.

FETCH  (First/Next)  <No.of Rows>  ROWS ONLY : Specifies the next  number of rows to be returned.

Limitations of OFFSET FETCH:

1. OFFSET FETCH can only be Used with ORDER BY Clause.
2. TOP caluse cannot be used with OFFSET FETCH caluse.
3. OFFSET FETCH rowcount be expression,parameter,constant.

Examples:

Skips the first 10 rows and returns rest of the rows from person table.

select Firstname+' '+LAstName as NAME,BusinessEntityID from person.person ORDER BY BusinessEntityID OFFSET 10 ROWS

Skipping the First 10 rows and fetching the next 5 rows

select Firstname+' '+LAstName as NAME,BusinessEntityID from person.person ORDER BY BusinessEntityID OFFSET 10 ROWS FETCH NEXT 5 ROWS only

Passing the Rowcounts as parameters

Declare @Startposition int=15,@nextset int=10

select Firstname+' '+LAstName as NAME,BusinessEntityID from person.person ORDER BY BusinessEntityID OFFSET @Startposition ROWS FETCH NEXT @nextset ROWS ONLY