Offset & Fetch

Offset & Fetch

Offset & Fetch: One of the new features in SQL Server 2012 is OFFSET & FETCH. For fetching records within in a range which can be used in Paging(i.e. records per page), can be achieved with the help of Offset & Fetch. It can be used in place of ROWNUMBER() (which was there for Paging before SQL Server 2012) method.

Syntax of Offset & Fetch:

ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[ < offset_fetch> ]

<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression }
    { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression }
    { ROW | ROWS } ONLY
    ]
}

Example of Offset & Fetch:

I am using Northwind Database.

SELECT EmployeeID, LastName, FirstName, Title, HireDate
FROM
dbo.Employees
ORDERBY HireDate DESC
OFFSET 0 ROWS
FETCHNEXT 10 ROWS ONLY;

In this example, the result set will return first 10 records on the result filtered with Order By Clause.

With this (OFFSET & FETCH), developer can also use variables to put dynamic values in OFFSET & FETCH NEXT like,

DECLARE @offSet INT= 0;
DECLARE @noOfRows INT= 10;
SELECT EmployeeID, LastName, FirstName, Title, HireDate
FROM
dbo.Employees
ORDERBY HireDate DESC
OFFSET @offSet ROWS
FETCHNEXT @noOfRows ROWS ONLY;

For complete post go to SQL Server 2012 Enhancements.