Esat Erkec
What is pagination in SQL Server?

Pagination in SQL Server

April 14, 2020 by

Pagination is a process that is used to divide a large data into smaller discrete pages, and this process is also known as paging. Pagination is commonly used by web applications and can be seen on Google. When we search for something on Google, it shows the results on the separated page; this is the main idea of the pagination.

The answer to the “What is pagination” question

Now, we will discuss how to achieve pagination in SQL Server in the next parts of the article.

Preparing Sample Data

Before beginning to go into detail about the pagination, we will create a sample table and will populate it with some synthetic data. In the following query, we will create a SampleFruits table that stores fruit names and selling prices. In the next part of the article, we will use this table.

Sample table for paging in SQL

What is Pagination in SQL Server?

In terms of the SQL Server, the aim of the pagination is, dividing a resultset into discrete pages with the help of the query. When the OFFSET and FETCH arguments are used in with the ORDER BY clause in a SELECT statement, it will be a pagination solution for SQL Server.

OFFSET argument specifies how many rows will be skipped from the resultset of the query. In the following example, the query will skip the first 3 rows of the SampleFruits table and then return all remaining rows.

What is pagination in SQL Server?

When we set OFFSET value as 0, no rows will be skipped from the resultset. The following query can be an example of this usage type:

OFFSET argument usage in SQL Server

On the other hand, if we set the OFFSET value, which is greater than the total row number of the resultset, no rows will be displayed on the result. When we consider the following query, the SampleFruits table total number of the rows is 13, and we set OFFSET value as 20, so the query will not display any result.

OFFSET argument usage in SQL Server

FETCH argument specifies how many rows will be displayed in the result, and the FETCH argument must be used with the OFFSET argument. In the following example, we will skip the first 5 rows and then limit the resultset to 6 rows for our sample table.

OFFSET – FETCH arguments usage for paging in SQL Server

Tip: The TOP CLAUSE limits the number of rows that returned from the SELECT statement. When we use the TOP clause without ORDER BY, it can be returned to arbitrary results. When we consider the following example, it will return 3 random rows on each execution of the query.

TOP clause usage in SQL Server

As we learned, the OFFSET-FETCH argument requires the ORDER BY clause in the SELECT statement. If we want to implement an undefined order which likes the previous usage of the TOP clause with OFFSET-FETCH arguments, we can use a query which looks like below:

OFFSET-FETCH usage instead of the TOP clause

Pagination query in SQL Server

After figuring out the answer to “What is Pagination?” question, we will learn how we can write a pagination query in SQL Server. At first, we will execute the following query and will tackle the query:

What is pagination in SQL Server

As we can see, we have declared two variables in the above query, and these variables are:

  1. @PageNumber – It specifies the number of the page which will be displayed
  2. @RowsOfPage – It specifies how many numbers of rows will be displayed on the page. As a result, the SELECT statement displays the second page, which contains 4 rows

Dynamic Sorting with Pagination

Applications may need to sort the data according to different columns either in ascending or descending order beside pagination. To overcome this type of requirement, we can use an ORDER BY clause with CASE conditions so that we obtain a query that can be sorted by the variables. The following query can be an example of this usage type:

Dynamic sorting with pagination

Also, we can change the sort column and sorting direction through the variables for the above query.

Pagination in a Loop

In this example, we will learn a query technique that returns all discrete page results with a single query.

What is pagination in SQL Server

For this query, we created a pretty simple formula. At first, we assigned the total row number of the SampleFruit table to the @MaxTablePage variable, and then we divided it into how many rows will be displayed on a page. So, we have calculated the number of pages that will be displayed. However, the calculated value can be a decimal, and for that, we used the CEILING function to round it up to the smallest integer number that is bigger than the calculated number. As a second step, we implemented a WHILE-LOOP and iterated @PageNumber variable until the last page of the number.

Conclusion

In this article, we tried to find out the answer to “What is Pagination?” question, particularly for SQL Server. OFFSET-FETCH arguments help to implement how many rows we want to skip and how many rows we want to display in the resultset when we use them with the ORDER BY clause in the SELECT statements. And finally, we learned how we can achieve pagination in SQL Server with these arguments.

Esat Erkec
32,921 Views