SQL Server paging using limit and offset with non-unique ordering column

user_1882699 0
Viewed 212

I would like to implement paging using SQL Server limit and offset as shown below. My question is, will SQL Server guerantee the correct data page when data is ordered by non-unique column?

SELECT * FROM TableName
ORDER BY NonUniqueColumn
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY
OPTION (recompile)

SELECT * FROM TableName
ORDER BY NonUniqueColumn
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY
OPTION (recompile)

These two selects overlap. Will the second select (second page) contain the last ten rows from the first select?

sql-server-2016 sql-server
1 Answers

will SQL Server guerantee the correct data page when data is ordered by non-unique column?

No. Add the primary key columns to the ORDER BY to guarantee a stable ordering. EG

SELECT * FROM TableName
ORDER BY NonUniqueColumn, Id
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY
OPTION (recompile)
user_72977000
Related