I have been out of touch with latest features in TSQL for a while now. That mostly happened because my dba gigs in the past few years have not involved a lot of programming. To come upto speed with TSQL, am working with a book TSQL recipes written by my good friends Wayne Sheffield(blog|twitter), Jason Brimhall(blog|twitter) and some others. Am going to pick one ‘recipe’ from the book for each blog post and write of what I learnt from it.  Today’s recipe is a simple problem –

‘You wish to present a result set to an application user n rows at a time, how will you do it’.

This lead me to research two keywords in TSQL – OFFSET and FETCH NEXT.These are used together. What Offset does is to tell the
resultsets to skip first <x> rows, and Fetch Next <y> rows grabs the y rows after the offset. So suppose my requirement at a more refined level is to return 100 products at a time to the application, ordered by product id – I would go for a stored procedure  as below:

CREATE PROCEDURE Paging100Rows

@cycle INTEGER

AS

DECLARE @offsetrows INTEGER, @fetchrows INTEGER

SET @offsetrows = @cycle

SET @fetchrows = 100

SELECT ProductID,[Name] FROM [Production].[Product]

ORDER BY [ProductID]

OFFSET @offsetrows ROWS FETCH NEXT @fetchrows ROWS ONLY;

So if I wanted the first 100 rows, I would call it as below..for the next 100 rows I’d pass 100 and the next 100 rows 200 and so on.

What to look out for:

1 The sort order must be very specific and related to the rows you want returned.
2 Any change in isolation level will impact results with dirty reads/non repeatable reads etc. (as it does with any select statement of any kind)

Related links:
https://technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx