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.
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.