SQL Server 2019: Trouble with Pagination on Large Result Sets Using OFFSET-FETCH
I've been banging my head against this for hours. I've been banging my head against this for hours. I'm having difficulty implementing pagination on a large result set in SQL Server 2019 using the `OFFSET-FETCH` clause. My goal is to retrieve a specific page of results from a large dataset efficiently, but I'm working with performance optimization and unexpected behavior in the results. Here's my query: ```sql SELECT * FROM Orders ORDER BY OrderDate DESC OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY; ``` I've set `@Offset` to 100 and `@PageSize` to 10 to fetch the 11th to 20th records. However, I'm noticing that the records returned are not what I expect. For instance, on the second page, I sometimes get duplicate records from the previous page, or occasionally, some records seem to be missing altogether. I suspect that the scenario might be related to the ordering of the results. The `OrderDate` column has some duplicate values, which could be causing the pagination to behave inconsistently. To tackle this, I tried adding a secondary sort on the `OrderID` like this: ```sql SELECT * FROM Orders ORDER BY OrderDate DESC, OrderID DESC OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY; ``` But I still see issues with the results. Additionally, I’m aware of best practices for pagination, such as avoiding large offsets and considering keyset pagination, but I haven’t implemented that yet because I want to understand what’s going wrong with my current approach. Could the duplicates in the `OrderDate` field be the root cause of this question? Are there specific strategies I should consider for improving the pagination mechanism in this context? Any insights or examples would be greatly appreciated! For context: I'm using Sql on Ubuntu. Am I missing something obvious? My team is using Sql for this REST API. Is this even possible?