Using the New OFFSET and FETCH NEXT Options

When using forms to show results from SQL Server databases, you often encounter the problem of trying to fit a fixed number of sorted rows into a form designed with a fixed size. In SQL Server 2008 R2 and earlier, you have to execute queries that retrieve all the data, then use different search features to locate the rows of data you want to display in the form.

Related: OFFSET/FETCH Part 1 and OFFSET/FETCH Part 2

In SQL Server 2012, there are two new arguments in the SELECT statement's ORDER BY clause that let you retrieve a fixed number of rows:

Here's the syntax for a simple SELECT statement that uses these arguments:

SELECT * FROM ORDER BY OFFSET  ROWSFETCH NEXT  ROWS ONLY

Let's walk through an example. Suppose you want to show 20 rows from the Products table, starting from row 11 in the result set, which you want ordered by the product name. In the form, you want the page-up button to increase the offset by 20 and the page-down button to decrease it by 20 (after checking limits).

To implement this solution, you'd use code such as:

SELECT * FROM dbp.Products AS PORDER BY P.productNameOFFSET 10 ROWSFETCH NEXT 20 ROWS ONLY

Note that you can use T-SQL variables instead of constants for the OFFSET and FETCH NEXT expressions.

If you've installed SQL Server 2012, check out these new arguments. They can be quite useful for retrieving a fixed number of rows from output that's been sorted.