The SOQL Pagination feature is now generally available in Summer ’12. This feature includes the new OFFSET clause used in Salesforce Object Query Language (SOQL) queries. What exactly does OFFSET do, you might be wondering?

OFFSET lets you specify a number of rows to skip in the returned data. So, for example if a SOQL query returns 1000 records (determined by using the COUNT() SOQL function) and you only want to see the last 50, you can add “OFFSET 950” to the end of your SOQL query:

SELECT Name FROM MyObject__c ORDER BY Name OFFSET 950

Where OFFSET really comes in handy is when it’s used in combination with LIMIT to break up returned data into blocks of records, sometimes referred to as “pages”.

A common occurrence in web or mobile application development is having too much data to present to the user in a single view. Scrolling through hundreds of records on a mobile device is not exactly the most ideal user experience. With OFFSET and LIMIT, you can specify an exact “page” of the record set.

Imagine you have a custom object called Merchandise__c and want to display the data to the user. You’ve determined that the amount of records in Merchandise __c is very large, via a SOQL query like:

SELECT COUNT() FROM Merchandise __c

You’d like to display 20 records at a time to the user. To get the first “page” of 20 records, you can issue the following SOQL query:

SELECT Name FROM Merchandise __c ORDER BY Name LIMIT 20

Which might return something like:

For the next page of 20 records, you can issue the following query:

SELECT Name FROM MyObject__c ORDER BY Name LIMIT 20 OFFSET 20

Which will return the next 20 records from the overall result set ordered by Name, perhaps something like:

For the third page, you’d use LIMIT 20 OFFSET 40 in your query. It’s that simple.

SOQL OFFSET can also be used in many of the Force.com features that use SOQL. If you’ve had difficulties using the built-in StandardSetController for pagination in your Visualforce pages, you can use SOQL OFFSET to write your own custom pagination implementation (make sure that you’ve verified that StandardSetController won’t work for you first, since this controller provides a lot of built-in functionality).

There are a couple things to keep in mind if you want to make your use of OFFSET as efficient as possible:

• You should always sort your results via ORDER BY. If you don’t, currently the records will be returned in a consistent order, but the algorithm used for the internal sort order is subject to change in the future, meaning your pages might not be consistent down the road.
• The maximum number of records you can skip via OFFSET is currently 2000 records.
• If you need behavior where the full result set is cached on the server side for efficiency reasons, you should use something like queryMore(), and not OFFSET.

More details on using OFFSET and considerations are in the SOQL OFFSET documentation in the SOQL and SOSL Reference Guide. For another nifty example using OFFSET, see Sandeep’s January 13 post in the Developer Relations blogs.