Thursday, March 7, 2013

SQL Server 2012 : Offset / Fetch

SQL Server 2012 launches Offset/Fetch which provide server side paging of results sets.

A Simplified example is this -

SELECT
  [columnlist]

FROM
  [table]
ORDER BY [column]

OFFSET [offset] ROWS
FETCH NEXT [rowcount] ROWS ONLY;


The new options are market in red above, and are -
  • [offset] - Position to start retrieving rows from.
  • [rowcount] - How many rows to retrieve

You've been able to achieve this in the past with a little ingenuity as the table below shows.

SQL Server Method Links

2012


OFFSET & FETCH New T-SQL features in SQL Server 2012 – OFFSET and FETCH

OFFSET – FETCH in SQL Server 2012

2005


ROW_NUMBER() T-SQL: Paging with ROW_NUMBER()

2000


SET ROWCOUNT Trick A More Efficient Method for Paging Through Large Result Sets

No comments: