Offset and Fetch Next Record occurs in multiple page

Question

Recently I hit a issue when use offset and fetch next to do pagination in SQL Server, some records occurs in multiple pages.

SELECT A.ID, A.Col1, A.Col2, A.Status
FROM (
    SELECT ID, Col1, Col2, Status
    FROM Table1
    ORDER BY Status
    OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY --# 100 just for sample, first page will be 0 and 100
) AS A
ORDER BY A.Status

ID is the primary key, and it occurs in different page, a lot of records with same status, but sequence in database should same based on my understanding. Now I use ROW_NUMBER without issue.

But still want to know any issue with OFFSET and FETCH NEXT, and how to fix this?

Answer 1

If there are multiple records with the same status, then order by status is not a stable clause; when there are ties, the database has to decide which record comes first, and this might not be consistent over consecutive executions of the same query.

To get a stable resultset, you need order by columns that whose combination represents a unique key. Here, a simple solution is to add the primary key to the clause:

So:

SELECT ID, Col1, Col2, Status
FROM Table1
ORDER BY Status, ID
OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY 

Note that you don't need a subquery: you can select, sort and fetch in the same scope.