SQL Tip: Breaking into Batches

Internet search results are typically broken into batches. The initial page displays results 1 through perhaps 10, page two shows matches 11–20 and so on. Splitting results up like this is known as (no surprise) paging.

To implement results paging with Microsoft SQL Server (version 2012 or later), try modifying your ORDER BY clause with OFFSET and FETCH. OFFSET tells SQL Server to start returning x rows into a result set. FETCH defines the number of rows to return.

In the below example, these modifiers are used to tell SQL Server to “skip over the first 10 results (OFFSET 10 ROWS) then return 10 rows (FETCH NEXT 10 ROWS ONLY)” or, to put it more concisely, “return matches 11–20.”

SELECT LastName, FirstName
FROM People
ORDER BY LastName, FirstName OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

Optimistic Paging

SQL Server’s OFFSET and FETCH provide optimistic paging. Each time a results page is retrieved, the provided query is executed. No caching of results occurs between page requests. The database server doesn’t think “they want the next page of results—let me send them the next 10 rows starting where we left off before.” Instead, a brand-new result set is computed for each request.

If the relevant data and its ordering remain unchanged between requests, optimistic paging works wonderfully. However, if data has changed or its ordering differs, results can be skipped over or returned multiple times.

Skipped Result Example

Suppose we have a list of names we’re retrieving in batches of three, ordered alphabetically. After fetching page one (the blue-bracketed rows on the left), the name Bert is inserted. The applied ordering places this new name on page one. However, page one has already been fetched so Bert will be skipped over. When page two is requested, Bert’s presence bumps Chris to page two (the blue-bracketed rows on the right) even though Chris was already included in page one.

Further Reading: Learn about Oracle’s OFFSET and FETCH and MySQL’s LIMIT which provide similar functionality on their respective database platforms.

Leave a Reply

Your email address will not be published. Required fields are marked *