Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Paging using Native T-SQL Commands
Published: Dec 03, 2020
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.