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
OFFSET ... ROWS
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
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).