Why pagination?

When building an API that returns data, you always have to keep the size of the response in mind. An API method that fetches data from a database, even with some filtering in place, can possibly return millions of records causing a big network traffic and eventually timeouts.

How to implement?

There are many ways to handle SQL pagination, but since SQL Server 2012 there is a new syntax available: the FETCH { FIRST | NEXT } , which provides a super easy way of handling this.

I will show you that approach and also the workaround that is normally used with a ROW_NUMBER() function.

Suppose you want to expose a REST API that is returning a lot of data from your application which will be used for integration purposes with another system. Then you can use the following steps:

Create the REST API:

First, you need to create a REST API in Outsystems (Read more), with 2 inputs in your new GET method. Name them PageSize and PageNumber and set them both to the type Integer.

REST API method example

Inputs:

PageSize: Size of the page. So, if you want to return 10 records, that will be the page size.

PageNumber: This will be the offset or the page to start the search. This is controlled by the requester.

Add Pagination to the SQL Query:

Second, you will need to get your data with a SQL query. I will now show you two Advanced query examples.

Let’s check the OFFSET and FETCH Pagination:

Using OFFSET and FETCH to limit the rows returned

Using OFFSET and FETCH as a paging solution requires running the query one time for each “page” of data returned to the client application. For example, to return the results of a query in 10-row increments, you must execute the query one time to return rows 1 to 10 and then run the query again to return rows 11 to 20 and so on. Each query is independent and not related to each other in any way. This means that, unlike using a cursor in which the query is executed once and state is maintained on the server, the client application is responsible for tracking state. Read more

The OFFSET and FETCH Read more

So, to achieve this, the query must be ordered ASC preferably by ID (if new records are added, the data returned will be consistent. If you order by another attribute or different order — DESC — if new records are added you will miss those.)

Then you just need to add the OFFSET and FETCH NEXT as shown below:

The OFFSET clause specifies the number of rows to skip.

The FETCH clause specifies the number of rows to return after the OFFSET clause has been processed.

The OFFSET clause is mandatory while the FETCH clause is optional.

The FIRST and NEXT are synonyms respectively so you can use them interchangeably.

Read more

How to calculate the Offset?

PageSize*(PageNumber-1)

With this expression set, you can send the offset as an input parameter to the query. The input PageSize, you get from the input parameter of the API method.

Using ROW_NUMBER()

Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Read more

The following example calculates row numbers for all rows in the RentalBooking table ordered by RentalBookingId and returns only rows that are between the offset and the limit of the offset.

How to calculate the limit in the previous example?

(PageSize*PageNumber)

And the Offset?

PageSize*(PageNumber-1)+1

So, when you have the inputs:

PageSize: 10

PageNumber:5

Then the WHERE clause will look like: