Implementing Paging Using OFFSET FETCH NEXT in SQL Server

A database can have hundreds of thousands of records. It is easy to insert and select these records via database management systems like SQL Server or MySQL etc. However, it is not easy to display thousands of records on a single webpage or in a desktop application. Space and memory constraints make it difficult to display a huge number of records all at once.

A common solution to such a problem is to implement paging. (Note, this is not memory paging implemented by operating systems) Paging in programming refers to displaying data via a series of pages. A random Google search may result in thousands of results. Google uses paging to display these results. If you scroll down the Google page with search results you would see the following:

Here you can see the number of pages that the search result is divided into. You can click the Next link to see more pages.

In this article, we will see how OFFSET FETCH NEXT operators can be used to implement paging on front-end applications. We will begin with a simple example using the OFFSET FETCH NEXT operator and will then see how it can be practically used with the help of a stored procedure.

Using OFFSET FETCH NEXT for Paging in SQL Server

SQL Server contains the OFFSET & NEXT operators to implement paging. The OFFSET operator offsets the next K number of search results from the beginning, while the FETCH NEXT operator fetches the NEXT N results where K and N are integers.

Preparing Dummy Data

Before we can see OFFSET FETCH NEXT in action, let’s create a dummy database with 200 records. You can use a working database if you are 100% confident that it is properly backed up. Execute the following script to do so:

CREATE Database ShowRoom;
GO
USE ShowRoom;

CREATE TABLE Cars
(
id INT PRIMARY KEY IDENTITY,
name VARCHAR(50) NOT NULL,
company VARCHAR(50) NOT NULL,
power INT NOT NULL
)

In the script above we create a dummy database ShowRoom with one table called Cars. Let’s add some dummy records in this database. Execute the following script:

 USE ShowRoom
DECLARE @count INT
SET @count = 1

DECLARE @carname VARCHAR (50)
DECLARE @company_name VARCHAR (50)

 WHILE (@count <= 200)
 BEGIN
	  SET @carname = 'Car - ' + LTRIM(@count)
	  SET @company_name = 'Company - '+ LTRIM(@count)
	  INSERT INTO Cars VALUES (@carname, @company_name, @count * 5)
	  SET @count = @count + 1
END

Take a careful look at the above script. The above script inserts 200 dummy records into the Cars table. The script uses a while loop for 200 iterations. Each iteration appends the word ‘Car -’ to the iteration number and the result is inserted into the name column of the Cars table. Similarly, the word “Company -” is appended with the iteration number and is inserted into the company column in each iteration. Finally, with each iteration, the iteration number is multiplied by 5 and the result is inserted into the power column. Now if you select all the records from the Cars table you will see 200 records in the result set. Execute the following query to do so:

SELECT * FROM Cars

Screenshot of the partial result of the above query is as follows. You can see 200 rows in the result.

OFFSET FETCH NEXT Example

Now let’s look at OFFSET NEXT in action. The syntax of OFFSET NEXT is as follows:

SELECT * FROM Table_Name
ORDER BY COLUMN_NAME/S
OFFSET Number_of_rows_to_Skip ROWS
FETCH NEXT Number_of_rows_to_Fetch ROWS ONLY

It is important to mention here that you have to use the ORDER BY clause with OFFSET FETCH NEXT clauses.

Let’s see a simple example of OFFSET FETCH NEXT where we will order the data by the id column of the Cars table, skip the first 20 rows, and fetch the next 10 rows. Execute the following script:

USE ShowRoom
SELECT * FROM Cars
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

In the output of the script above, you will see records with an id value of 21 to 30 since we skipped the first 20 records and fetched the next 10.

Using OFFSET FETCH NEXT with Stored Procedure

If you are implementing paging in a front end application such as a website or a desktop application, you will typically send page number and page size values to the server via a stored procedure. Depending upon the value of the page number and page size, the stored procedure will return the correct set of rows. Let’s write such a stored procedure which takes page number and page size as parameters and returns the corresponding records.

Take a look at the following script:

USE ShowRoom
GO
CREATE PROC spGetRecordsByPageAndSize
@Page INT,
@Size INT
AS
BEGIN
	SELECT * FROM Cars
	ORDER BY id
	OFFSET (@Page -1) * @Size ROWS
	FETCH NEXT @Size ROWS ONLY
END

In the script above we create a stored procedure spGetRecordsByPageAndSize that takes 2 parameters @Page and @Size. The stored procedure uses OFFSET FETCH NEXT to filter records by a number of pages and page size. For instance, if the page number is 2, and the size is 20, the OFFSET will be:

(2 – 1) * 20 = 20

And the value for FETCH next will be equal to the @Size i.e. 20. Therefore the records with id 21 to 40 will be returned. Execute the above script to create a stored procedure.
Once you have created the stored procedure, execute the following script to see what is returned when the page number is 2 and page size is 20.

EXECUTE spGetRecordsByPageAndSize 2, 20

The output of the above script looks like this:

Similarly, if you want to retrieve records for the 4th page with 15 records per page, the following query retrieves the records from id 46 to id 60.

EXECUTE spGetRecordsByPageAndSize 4, 15

The output of looks like this:

Conclusion

OFFSET FETCH NEXT is an extremely useful tool, particularly if you want to display a large number of records grouped into pages. In this article, we saw how it is used in conjunction with a stored procedure to implement paging on front-end applications.