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:
@PageSize bigint = 10,
@PageNumber integer = 3;
WITH Numbered AS
SELECT TOP ((@PageNumber + 1) * @PageSize)
rn = ROW_NUMBER() OVER (
ORDER BY o.[object_id])
FROM #objects AS o
FROM Numbered AS x
x.rn >= @PageNumber * @PageSize
AND x.rn < ((@PageNumber + 1) * @PageSize)
FROM #objects AS o
OFFSET @PageNumber * @PageSize - 1 ROWS
FETCH NEXT @PageSize ROWS ONLY;
ROW_NUMBER plan has an estimated cost of 0.0197935:
OFFSET plan has an estimated cost of 0.0196955:
That is a saving of 0.000098 estimated cost units (though the
OFFSET plan would require extra operators if you want to return a row number for each row). The
OFFSET plan will still be slightly cheaper, generally speaking, but do remember that estimated costs are exactly that - real testing is still required. The bulk of the cost in both plans is the cost of the full sort of the input set, so helpful indexes would benefit both solutions.
Where constant literal values are used (e.g.
OFFSET 30 in the original example) the optimizer can use a TopN Sort instead of a full sort followed by a Top. When the rows needed from the TopN Sort is a constant literal and <= 100 (the sum of
FETCH) the execution engine can use a different sort algorithm which can perform faster than generalized TopN sort. All three cases have different performance characteristics overall.
As to why the optimizer does not automatically transform the
ROW_NUMBER syntax pattern to use
OFFSET, there are a number of reasons:
- It's almost impossible to write a transform that would match all existing uses
- Having some paging queries automatically transformed and not others could be confusing
OFFSET plan is not guaranteed to be better in all cases
One example for the third point above occurs where the paging set is quite wide. It can be much more efficient to seek the keys needed using a nonclustered index and manually lookup against the clustered index compared with scanning the index with
ROW_NUMBER. There are additional issues to consider if the paging application needs to know how many rows or pages there are in total. There is another good discussion of the relative merits of the 'key seek' and 'offset' methods here.
Overall, it is probably better that people make an informed decision to change their paging queries to use
OFFSET, if appropriate, after thorough testing.