Thursday, June 17, 2010

ADS 10 Tip #14 – Paging with SQL

The ability to divide the results of a query into smaller pieces is a very common need for disconnected or partially connected applications. These smaller resultsets can be much more efficient than passing back the entire result and allow for paging. For example; It is very common to see  controls like the ones shown below after a web search.

Paging

You can use the TOP function to limit the results of a query to a specific number of records or a percentage of the resultset. This command has been available since version 8. With version 10 we have added the START AT functionality which allows for paging. The basic syntax is TOP x START AT y, where x is the number of records to return and y is the first record in the result.

// Get the first 10 customers
SELECT TOP 10 
  CustNum, LastName, FirstName
  FROM Customer
  ORDER BY LastName
 
// Get the next 10 customers
SELECT TOP 10 START AT 11
  CustNum, LastName, FirstName
  FROM Customer
  ORDER BY LastName;

If you set the START AT value greater than the number of records an empty resultset will be returned. You cannot use parameters as the values, your logic must build the appropriate statement as you get additional pages.

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

No comments: