Click on the banner to learn about and purchase my database training on Azure

SQL Server 2012 - How to create pagination of data in the results of a query with OFFSET and FETCH

Views: 4.286 views
Reading Time: 3 minutes

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:


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:


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

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!