Lance England

Data | Analysis | Integration | Automation

T-SQL Study: OFFSET FETCH

Continuing the T-SQL study series, the award for “Most Awkward Implementation” goes to… OFFSET FETCH!

To be fair, this is not on Microsoft. This is an ANSI implementation, and for that Microsoft should be commended. It’s too bad the standard is a little wordy and klunky. Enough whinging, here’s the scoop.

Paging through result sets, the preferred way is now with OFFSET/FETCH:

The klunky syntax:

OFFSET n ROW[S] FETCH [FIRST\|NEXT} n ROW[S] ONLY

Note:

DECLARE @ResultsPerPage INT = 10;
DECLARE @PageNumber INT = 0;

SELECT SalesOrderNumber, OrderQuantity, ProductKey  
FROM dbo.FactInternetSales
ORDER BY ORDERDATE
OFFSET (@PageNumber * @ResultsPerPage) ROWS FETCH NEXT @ResultsPerPage ROWS ONLY;
24 Mar 2014 Permalink data