SQL Server 2019: Unexpected behavior with OFFSET-FETCH when using ORDER BY with multiple columns
I'm stuck trying to I'm trying to configure I'm working on a paginated query using SQL Server 2019 with OFFSET and FETCH. The query is designed to return a set of rows based on pagination parameters, but I’m encountering unexpected results when ordering by multiple columns. Specifically, when I add a secondary sort column, the output seems inconsistent, and I sometimes get duplicate rows in the result set. Here's a simplified version of my SQL query: ```sql DECLARE @PageNumber INT = 2; DECLARE @PageSize INT = 10; SELECT Id, Name, CreatedDate FROM Products ORDER BY CategoryId, CreatedDate DESC OFFSET (@PageNumber - 1) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY; ``` In this example, I'm trying to paginate through a list of products, ordered first by `CategoryId` and then by `CreatedDate` in descending order. However, I'm noticing that if multiple products belong to the same category and have the same `CreatedDate`, the rows aren’t consistently ordered, which leads to duplicates appearing on different pages. I’ve attempted to resolve this by adding a third sorting column, `Id`, to ensure a unique order: ```sql ORDER BY CategoryId, CreatedDate DESC, Id ``` Yet, the issue persists. I also checked if there are any duplicate entries in my `Products` table, and they seem to be unique based on the primary key, which is `Id`. Is there something specific I’m missing with the OFFSET-FETCH clause when dealing with multiple ORDER BY columns? How can I ensure that my pagination results are consistent without duplicates across different pages? I'm working on a service that needs to handle this. Has anyone dealt with something similar? What's the best practice here? I'd really appreciate any guidance on this.