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, so less data is processed on output and queries have a response time. smaller. This scenario, which is quite common in applications that often paginate data on screen, both to avoid information overload and to shorten the time required to load information.
With the advent of ROW_NUMBER () in SQL Server 2005, many people started using this function to create data pagination, working like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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, from SQL Server 2012 we have the native paging functionality in SQL Server itself which many people end up not using unknowingly. We're talking about OFFSET and FETCH, which work together to allow us to page our results before displaying and sending them to applications and customers.
See with its use is simple:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 9 |
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 look at the performance of both queries to see if beyond ease we have any performance advantage:
Execution Plan - ROW_NUMBER ()
Execution plan - OFFSET and FETCH
Looking at the execution plan, the solution with OFFSET and FETCH seems to use fewer operators and present a simpler plan. Let's now the numbers in practice:
I will change the queries and try to sort by an unindexed VARCHAR column to check if there is any change in the performance of 2 solutions:
Well, analyzing the results of the tests I performed, it was clear that although this solution is more practical to implement, it is a little behind in performance.
I believe in a real day to day scenario, this performance difference turns out not to be as relevant (depending on the application usage), but is the tip on the cost-benefit ratio between practicality and performance when using paging 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 wayby Aaron Bertrand.
I hope you enjoyed 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!
very good the comparison!