Hey guys!

In this post, I would like to demonstrate to you how to create data pagination in SQL Server, so that only a limited amount of records are returned by queries, causing less data to be processed in the output and queries have a shorter response time. This scenario is quite common in applications, which tend to page data on the screen, both to avoid excess information and to reduce the time needed to load the information.

With the appearance of ROW_NUMBER() in SQL Server 2005, many people started using this function to create data pagination, working like this:

DECLARE
    @Pagina INT = 5,
    @ItensPorPagina INT = 10


SELECT *
FROM (
    SELECT [name], ROW_NUMBER() OVER(ORDER BY [name]) AS Ranking
    FROM sys.objects
) A
WHERE 
    A.Ranking >= ((@Pagina - 1) * @ItensPorPagina) + 1 
    AND A.Ranking < (@Pagina * @ItensPorPagina) + 1

Result:

However, starting with SQL Server 2012, we have the native paging functionality in SQL Server itself, which many people end up not using due to lack of knowledge. We are talking about the OFFSET and FETCH feature, which work together to allow us to page our results before displaying and sending them to applications and clients.

See how it’s used, it’s simple:

SELECT [name]
FROM sys.objects
ORDER BY [name]
OFFSET 40 ROWS -- Linha de início: Vai começar a retornar a partir da linha 40
  FETCH NEXT 10 ROWS ONLY -- Quantidade de linhas para retornar: Vai retornar as próximas 10 linhas

Result:

And if we want something parameterizable, just like the previous example, we can use the same structure as the previous example:

DECLARE
    @Pagina INT = 5,
    @ItensPorPagina INT = 10

SELECT [name]
FROM sys.objects
ORDER BY [name]
OFFSET (@Pagina - 1) * @ItensPorPagina ROWS
  FETCH NEXT @ItensPorPagina ROWS ONLY

Let's now analyze the performance of the two queries to see if, in addition to ease, we have any advantage in terms of performance:

Execution Plan – ROW_NUMBER()

Execution plan – OFFSET and FETCH

Analyzing the execution plan, the solution with OFFSET and FETCH seems to use fewer operators and present a simpler plan. Now let’s look at the numbers in practice:

I'm going to change the queries and try to sort by a VARCHAR column, not indexed, to check if there is any change in the performance of the 2 solutions:

Well, analyzing the results of the tests I carried out, it became clear that, although this solution is more practical to implement, it falls a little behind in terms of performance.

I believe that in a real day-to-day scenario, this difference in performance ends up not being so relevant (depending on the use of the application), but here is a tip about the cost-benefit relationship between practicality and performance when using pagination in SQL Server with ROW_NUMBER() or OFFSET + FETCH. If you want to delve deeper into the performance analysis of OFFSET + FETCH, I recommend reading the article Pagination with OFFSET/FETCH: A better way, by Aaron Bertrand.

I hope you liked this post, where I presented how to create data pagination in SQL Server using ROW_NUMBER() or OFFSET + FETCH NEXT n ROWS ONLY.
A hug and see you next time!