SQL Server 2017: Unexpected behavior with CTE and ROW_NUMBER() in pagination queries
Hey everyone, I'm running into an issue that's driving me crazy... I'm currently working with SQL Server 2017 and I've run into an issue with a Common Table Expression (CTE) that uses `ROW_NUMBER()` for pagination. My goal is to implement pagination for a large dataset, but I'm noticing unexpected results when the data set has duplicate values in the ordering column. Here's the CTE I wrote: ```sql WITH OrderedData AS ( SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY Name) AS RowNum FROM MyTable ) SELECT * FROM OrderedData WHERE RowNum BETWEEN 1 AND 10; ``` When I run this query, I sometimes get different rows for the same page when I execute it multiple times, even though the underlying data hasn't changed. It looks like the `ROW_NUMBER()` function is assigning different row numbers to rows with the same `Name` value, which is leading to inconsistent pagination results. I tried adding additional columns to the `ORDER BY` clause to make it more unique: ```sql WITH OrderedData AS ( SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY Name, Id) AS RowNum FROM MyTable ) ``` While this does help, I've noticed that it still doesn't always produce consistent results when my dataset has a lot of duplicates in the `Name` column. What’s the recommended approach to ensure consistent pagination results in this situation? Is there a better way to handle pagination in SQL Server when dealing with potential duplicates in the ordered column? Any insights or best practices would be greatly appreciated!