Paging using Native tSQL Commands

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:

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

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.

Leave a Comment

Your email address will not be published. Required fields are marked *