sql server 2012 offset return same record

Question

I have a strange problem with SQL Server 2012.

I use a query like this

SELECT * FROM table ORDER BY field OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY

Every time I use this query from 0 to 25, 25 to 50, 50 to 75, 75 to 100, it returns the same 25 records.

When I use it with 100 to 125, it returns the original 25 to 50 etc...

My table contains only 197 records and this happens only with one table of my database. All other tables work correctly.

This also happens when I use the query via code (ASP.NET C#) and from SQL Management Studio directly.

Answer 1

I had the same issue: the query always returned the same rows independently on the Offset value (30):

OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY 

For me this comment by veljasije is the correct answer:

Weird, maybe your column in ORDER BY is not deterministic, and make problem. Can field column can contain duplicates? If so, add primary key column in ORDER BY clause, after field column.

I solved this issue by adding [ID] column into Order By:

ORDER BY [Status], [ID] ASC

Answer 2

You must change your logic a little bit, to include which page is active:

DECLARE @pagesize AS BIGINT = 25, @pagenum AS BIGINT = 3; 

SELECT * 
FROM table 
ORDER BY field 
OFFSET @pagesize * @pagenum ROWS FETCH NEXT @pagesize ROWS ONLY;