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
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)