Spring ’12 is upon us and it comes bearing plenty of gifts for the Force.com developer. One of more important features in Spring ’12 is the addition of the “OFFSET” clause to SOQL. With this clause, you can now paginate through your result set directly in SOQL. Prior to this feature, there were a couple of ways to implement pagination in Force.com. When using one of the Force.com APIs (SOAP or REST), you already had pagination built in via the query/queryMore calls. In the context of pagination in a Visualforce page, the ideal implementation was to use a StandardSetController. However many developers also chose to implement their own custom pagination logic using custom SOQL queries and/or with some client side JavaScript. The new OFFSET clause should make any custom pagination logic on the Force.com platform really simple. Lets take a closer look at this new clause.

As an example, say you wanted to develop a Visualforce page for paginating over a set of Account records. As stated earlier, the ideal implementation for this use case is still to use a StandardSetController, but for the purposes of illustration lets see how you could implement this using the new OFFSET clause. You can find the entire code for this sample on GitHub, but lets focus on the Apex extension class for now.

Each time that the user clicks on the “Next’ or ‘Previous’ link on the page, the pageNumber variable is updated and the queryAccounts method is called. This is where the OFFSET magic happens. The OFFSET clause specifies the starting row offset into the result set returned by your query. So for example if your query returned a total of 30 Account records, adding a ‘ LIMIT 10 OFFSET 20’ clause would return rows 21-30. This is the exact logic implemented in the queryAccounts method.

A couple of additional notes on the use of the OFFSET clause

• This feature is currently in Developer Preview and you’ll need to contact Salesforce support to enable it in your Org.
• It is usually a best practice to use a ORDER BY clause when you use OFFSET  (as shown above) to ensure that the result set ordering is consistent.
• Similarly, it is recommended that you use a LIMIT clause in combination with OFFSET if you need to retrieve subsequent subsets of the same result set. That’s how for example the queryAccounts method can keep getting the next/previous set of 10 Account records from the initial result set.
• OFFSET is applied to the result set returned at the time of the query. The paging results may change if the underlying data is modified (for example if someone inserts/deletes records) between multiple queries using OFFSET into the same result set. If this is of concern to your design, you’ll need to use the query/queryMore pattern of the API instead.