Paging using Native T-SQL Commands
Published: Nov 28, 2021
One of the most common requirements within websites and other application is the ability to page results. This used to be an onerous task and, in many cases I’ve seen, people have tended to simply pull a full dataset from SQL Server, cache the results in the application, and then let the application itself page out the results accordingly. However, there is an easier way to do this directly from within SQL Server without using complex code.

Let’s say we’re trying to page out the Sales Orders from AdventureWorks by SalesOrderID. An older way of doing this might be as follows:

use AdventureWorks2012
go

declare @rowsPerPage int, @pageNo int

select
@rowsPerPage = 50, @pageNo = 2;

with ordering as
(
select row_number() over(order by salesOrderID) as id, *
from sales.salesOrderHeader
)
select *
from ordering
where id between ((@pageNo - 1) * @rowsPerPage) + 1 and (@pageNo * @rowsPerPage)
order by SalesOrderID



This works just fine, but there is a more native way to code this which uses OFFSET:

declare @rowsPerPage int, @pageNo int

select
@rowsPerPage = 50, @pageNo = 2;

select *
from sales.salesOrderHeader
order by SalesOrderID
offset
((@pageNo - 1) * @rowsPerPage) rows
fetch next @rowsPerPage rows only



The other advantage to the OFFSET method is that you aren’t adding another column into the resultset when using *. Not that you should be using * in your queries, but we know how lazy developers tend to be.

Either way, this is a useful way of simply and cleanly obtaining pagination directly from your SQL Server and it’s also more efficient than using row_number which adds quite a bit of internal overhead. This was a VERY small example on a very small dataset, but even so you can see the difference in execution between the methods:


If I scale this up by using a much larger table (using my sales.salesOrderHeaderEnlarged table) the differences become more noticeable:


It’s also worth noting that you get the best performance it’s definitely recommended to have an index on your ordering column otherwise you can end up seeing a large overhead appear which you really don’t want to see.

Comments:
NB: Comments will only appear once they have been moderated.