Hot answers tagged offset-fetch
7
Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
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
Why are there execution plan differences between OFFSET … FETCH and the old-style ROW_NUMBER scheme?
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
