In the previous post, we saw the new TSQL syntax introduced in SQL Server 2011 to support server side paging. We saw the new TSQL keywords OFFSET and FETCH NEXT in the previous post. These new keywords are introduced as an extension to the ORDER BY clause. Let us examine them in detail.

OFFSET

OFFSET specifies the number of rows to skip before start retrieving the rows. This is evaluated after sorting the result set using the columns specified in the ORDER BY clause.

What is very interesting about OFFSET is that, you can specify a variable along with it. This is helpful to write generic handlers where the page size and current page number are passed as parameters.

DECLARE @offset INT
SELECT @offset = 10
SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET @offset ROWS 
FETCH NEXT 10 ROWS ONLY;

It can also be a function, subquery or a valid TSQL expression that returns 0 or a positive integer value. If the expression returns a negative value or NULL, the query will fail with an error.

SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET (SELECT offset FROM sometable) ROWS 
FETCH NEXT 10 ROWS ONLY;

OFFSET is followed by ROWS or ROW. You can use either one of them.

FETCH NEXT

FETCH can be used with either FIRST or NEXT and they have the same meaning. NEXT or FIRST is followed by the number of rows to retrieve. The number can be a constant value, result of a query or an expression. Here is an example that uses a sub-query to specify the number of rows to retrieve.

SELECT 
	*
FROM @t 
ORDER BY CustID  
OFFSET (SELECT offset FROM setuptable) ROWS 
FETCH NEXT (SELECT pagesize FROM setuptable) ROWS ONLY;

The expression (or sub-query) should return a positive integer value that is greater than 0. If 0, NULL or a negative value is specified the query will fail with an error.