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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE SampleFruits ( Id INT PRIMARY KEY IDENTITY(1,1) , FruitName VARCHAR(50) , Price INT ) GO INSERT INTO SampleFruits VALUES('Apple',20) INSERT INTO SampleFruits VALUES('Apricot',12) INSERT INTO SampleFruits VALUES('Banana',8) INSERT INTO SampleFruits VALUES('Cherry',11) INSERT INTO SampleFruits VALUES('Strawberry',26) INSERT INTO SampleFruits VALUES('Lemon',4) INSERT INTO SampleFruits VALUES('Kiwi',14) INSERT INTO SampleFruits VALUES('Coconut',34) INSERT INTO SampleFruits VALUES('Orange',24) INSERT INTO SampleFruits VALUES('Raspberry',13) INSERT INTO SampleFruits VALUES('Mango',9) INSERT INTO SampleFruits VALUES('Mandarin',19) INSERT INTO SampleFruits VALUES('Pineapple',22) GO SELECT * FROM SampleFruits |
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.
|
1 2 3 4 |
SELECT FruitName, Price FROM SampleFruits ORDER BY Price OFFSET 3 ROWS |
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:
|
1 2 3 |
SELECT FruitName,Price FROM SampleFruits ORDER BY Price OFFSET 0 ROWS |
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.
|
1 2 3 |
SELECT FruitName,Price FROM SampleFruits ORDER BY Price OFFSET 20 ROWS |
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.
|
1 2 3 4 |
SELECT FruitName, Price FROM SampleFruits ORDER BY Price OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY |
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.
|
1 2 |
SELECT TOP 7 FruitName, Price FROM SampleFruits |
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:
|
1 2 3 |
SELECT FruitName ,Price FROM SampleFruits ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY |
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:
|
1 2 3 4 5 6 7 8 |
DECLARE @PageNumber AS INT DECLARE @RowsOfPage AS INT SET @PageNumber=2 SET @RowsOfPage=4 SELECT FruitName,Price FROM SampleFruits ORDER BY Price OFFSET (@PageNumber-1)*@RowsOfPage ROWS FETCH NEXT @RowsOfPage ROWS ONLY |
As we can see, we have declared two variables in the above query, and these variables are:
- @PageNumber – It specifies the number of the page which will be displayed
- @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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @PageNumber AS INT DECLARE @RowsOfPage AS INT DECLARE @SortingCol AS VARCHAR(100) ='FruitName' DECLARE @SortType AS VARCHAR(100) = 'DESC' SET @PageNumber=1 SET @RowsOfPage=4 SELECT FruitName,Price FROM SampleFruits ORDER BY CASE WHEN @SortingCol = 'Price' AND @SortType ='ASC' THEN Price END , CASE WHEN @SortingCol = 'Price' AND @SortType ='DESC' THEN Price END DESC, CASE WHEN @SortingCol = 'FruitName' AND @SortType ='ASC' THEN FruitName END , CASE WHEN @SortingCol = 'FruitName' AND @SortType ='DESC' THEN FruitName END DESC OFFSET (@PageNumber-1)*@RowsOfPage ROWS FETCH NEXT @RowsOfPage ROWS ONLY |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @PageNumber AS INT DECLARE @RowsOfPage AS INT DECLARE @MaxTablePage AS FLOAT SET @PageNumber=1 SET @RowsOfPage=4 SELECT @MaxTablePage = COUNT(*) FROM SampleFruits SET @MaxTablePage = CEILING(@MaxTablePage/@RowsOfPage) WHILE @MaxTablePage >= @PageNumber BEGIN SELECT FruitName,Price FROM SampleFruits ORDER BY Price OFFSET (@PageNumber-1)*@RowsOfPage ROWS FETCH NEXT @RowsOfPage ROWS ONLY SET @PageNumber = @PageNumber + 1 END |
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.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023