This article presents an overview of two clauses which allow paging on results sets when added to the SELECT statement in SQL Server 2012.

When using windows or forms to show the results from a SQL Server database, I used to encounter a problem with fitting a fixed and sorted number of rows into a form designed with a fixed size. In earlier versions of SQL Server, SQL programmers executed queries that retrieved all the data, and afterwards used different search features in order to the locate the row of data they wanted. In SQL Server 2012, new clauses in the SELECT statement were introduced which allow you to retrieve a fixed number of sorted rows that will fit the size of the form shown to the client.

The solution involves using two new clauses to the SELECT statement.

OFFSET ... ROWS 

and

FETCH NEXT.... ROWS ONLY

OFFSET ... ROWS is used to indicate which line number to start with when retrieving results.

FETCH NEXT.... ROWS ONLY is used to indicate how many lines from line number in Expr1 to fetch in order to fit on the page.

An outline of the SELECT statement including these clauses would look like this:

SELECT * FROM...
ORDER BY.....
OFFSET ... ROWS
FETCH NEXT.... ROWS ONLY

Now, suppose you want to show 20 rows from 'Products Table', ordered by the product name, and starting from row number 11 in the results set. Page-up will increase the offset by 20 and Page-down will decrease it by 20 (after checking limits).

The SQL would look like:

SELECT * FROM dbo.Products P
ORDER BY P.productName
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY

The OFFSET expression and FETCH NEXT expression are TSQL variables that can be used in place of the constants. If you have SQL Server 2012 installed, check it out for yourself.

Note: This was tested on SQL Server 2012 RC0 version (Sample North wind DB SQL2012 compatible).