How to do Pagination in SQL Server? Query Example Tutorial

 What is pagination?

Pagination, often known as paging, is a method of breaking down huge amounts of material into smaller, distinct pages. Pagination is a popular feature of online applications, as evidenced by Google. The primary principle of pagination is that when we search for anything on Google, the results are displayed on a different page.

In the next sections of the post, we'll go through how to implement pagination in SQL Server.

Before we get into the nitty-gritty of pagination, we'll make a test table and populate it with some fictitious data. We'll establish a SampleFruits database to record fruit names and selling prices in the following query. This table will be used in the following section of the article.

Create the table : 

CREATE TABLE SampleFruits ( 
Id INT PRIMARY KEY, 
FruitName VARCHAR(50), 
Price INT
);

Insert the data : 

INSERT INTO SampleFruits VALUES(1,'Apple',20);
INSERT INTO SampleFruits VALUES(2,'Apricot',12);
INSERT INTO SampleFruits VALUES(3,'Banana',8);
INSERT INTO SampleFruits VALUES(4,'Cherry',11);
INSERT INTO SampleFruits VALUES(5,'Strawberry',26);
INSERT INTO SampleFruits VALUES(6,'Lemon',4);
INSERT INTO SampleFruits VALUES(7,'Kiwi',14);  
INSERT INTO SampleFruits VALUES(8,'Coconut',34); 
INSERT INTO SampleFruits VALUES(9,'Orange',24);  
INSERT INTO SampleFruits VALUES(10,'Raspberry',13);
INSERT INTO SampleFruits VALUES(11,'Mango',9);
INSERT INTO SampleFruits VALUES(12,'Mandarin',19);
INSERT INTO SampleFruits VALUES(13,'Pineapple',22);

Fetch the results to verify : 

SELECT * FROM SampleFruits;

The output will be something like below : 

What is pagination in an SQL server?

The goal of pagination in SQL Server is to divide a resultset into distinct pages with the aid of the query. It's a pagination solution for SQL Server when the OFFSET and FETCH arguments are used with the ORDER BY clause in a SELECT query.

The OFFSET option indicates how many rows from the query's resultset shall be skipped. The query in the following example skips the first three rows of the SampleFruits database and returns the remaining rows.

The query for the same is : 

SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 3 ROWS

The result of the query is something like below : 

When the OFFSET parameter is set to 0, no rows from the resultset are skipped.

If the OFFSET value is more than the entire number of rows in the result set, however, no rows will be displayed on the result. When we analyze the following query, the total number of entries in the SampleFruits database is 13, and we set the OFFSET value to 20, the query will return no results.

SELECT FruitName,Price FROM SampleFruits
ORDER BY Price 
OFFSET 20 ROWS

The result of the following query will be as expected, nothing! The FETCH option determines the number of rows that will be presented in the result, and it must be used in conjunction with the OFFSET argument. For our sample table, we'll omit the first 5 rows and restrict the resultset to 6 rows in the following example. SELECT FruitName, Price FROM SampleFruits ORDER BY Price OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY The TOP CLAUSE sets a restriction on how many rows the SELECT operation can return. When the TOP clause is used without the ORDER BY clause, the results might be arbitrary. When we take the following example, each query execution will return three random records. SELECT TOP 7 FruitName, Price FROM SampleFruits The ORDER BY phrase in the SELECT statement is required for the OFFSET-FETCH parameter, as we learned. If we wish to utilize an undefined order similar to the prior use of the TOP clause with OFFSET-FETCH parameters, we may use the following query: SELECT FruitName ,Price FROM SampleFruits ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY Pagination query in SQL server :   We'll learn how to build a pagination query in SQL Server when we figure out the answer to the question "What is Pagination?" We'll start by running the following query and then tackle the question: 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 you can see, we've specified two variables in the above query: @PageNumber — This defines the page number that will be shown. @RowsOfPage – This determines the number of rows that will be shown on the page. As a consequence, the second page, which includes four rows, is displayed by the SELECT command. Dynamic sorting with pagination :  Apart from pagination, applications may need to sort data by distinct columns in ascending or descending order. To meet this need, we can combine an ORDER BY clause with CASE conditions to create a query that can be ordered according to the variables. This usage type is exemplified by the following query: 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 The variables for the above query also allow us to adjust the sort column and sorting orientation. Conclusion :  We attempted to address the topic "What is Pagination?" in this post, with a focus on SQL Server. When used with the ORDER BY clause in the SELECT statements, the OFFSET-FETCH parameters assist to implement how many rows we want to skip and how many rows we want to display in the resultset. Finally, we learned how to use these parameters in SQL Server to accomplish pagination. Other SQL Articles and Tutorials you may like How to transpose data in SQL? How to find Nth Highest salary in SQL Difference between DDL and DML commands in SQL How to remove duplicate rows in SQL How to use ORDER BY in SQL with example How to concatenate columns in SQL How to use Cross Join in SQL 15 SQL Interview Questions with Answers How to use row_number function in SQL MySQL vs PostgreSQL? Pros and Cons What is a Virtual column in MySQL? Example Thanks for reading this article so far. If you like this SQL Pagination tutorial and example then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note.