Tag Info

Hot answers tagged

9

The examples in the question do not quite produce the same results (the OFFSET example has an off-by-one error). The updated forms below fix that issue, remove the extra sort for the ROW_NUMBER case, and use variables to make the solution more general: DECLARE @PageSize bigint = 10, @PageNumber integer = 3; WITH Numbered AS ( SELECT TOP ...


4

With a slight fiddling of your query I get an equal cost estimate (50/50) and equal IO stats: ; WITH cte AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r FROM #objects ) SELECT * FROM cte WHERE r >= 30 AND r < 40 ORDER BY r SELECT * FROM #objects ORDER BY object_id OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY This avoids the additional ...



Only top voted, non community-wiki answers of a minimum length are eligible