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:
- OFFSET <EXPR1> ROWS, which you use to specify the line number from which to start retrieving results
- FETCH NEXT <EXPR2> ROWS ONLY, which you use to specify how many lines to
Here's the syntax for a simple SELECT statement that uses these arguments:
SELECT * FROM <table> ORDER BY <columns> OFFSET <EXPR1> ROWS FETCH NEXT <EXPR2> 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 P ORDER BY P.productName OFFSET 10 ROWS FETCH 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.
