Pagination in SQL Server - huge records paging

 

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:

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