Pagination in SQL Server

By | 9th August 2019
0 Comment
Contents hide
1 Create table with dummy data
2 Pagination using ROW_NUMBER function
3 Pagination using OFFSET FETCH clause

In this article, let’s see various methods to implement pagination in SQL server.

In SQL server database, a table can have N no of records. Populating all these records in a grid control slows down the web page. You need to divide the number of records and populate it in the web page. Pagination is the process of splitting the number of records from a SELECT query result set into several pages. Based on the SQL server version, it can be implemented using ROW_NUMBER function or OFFSET FETCH clause.

Let’s create a sample table with some dummy data to see the implementation of pagination.

Create table with dummy data

Create a table ‘Course’ and insert dummy data using the below queries

CREATE TABLE Course
(
	id INT PRIMARY KEY IDENTITY,
	courseName VARCHAR(50) NOT NULL,
	department VARCHAR(50) NOT NULL
);

DECLARE @count INT = 1, @depCount INT = 1
WHILE (@count <= 200)
BEGIN
	INSERT INTO Course VALUES ('Course ' + LTRIM(@count), 'Department ' + LTRIM(@depCount))
	SET @count += 1;
	SET @depCount += 1;
	IF(@depCount % 10 = 0)
		SET @depCount = 1
END;

Now, the course table contains 200 records.

Pagination using ROW_NUMBER function

ROW_NUMBER function is introduced in SQL Server 2005. Let’s see how pagination can be implemented using this function. This method can be used in SQL server version 2005 and above.

DECLARE @PageNo INT = 1
DECLARE @RowsPerPage INT = 10 --Records Per Page

DECLARE @Start INT, @End INT --Start and end record number in a page
SET @Start = (@PageNo -1) * @RowsPerPage + 1
SET @End = @RowsPerPage * @PageNo

SELECT * FROM 
(
	SELECT *, ROW_NUMBER() OVER(ORDER BY department, id) AS RowNum 
	FROM Course
) AS Programs
WHERE RowNum BETWEEN @Start AND @End;

See the result below. It returns the first page with 10 records.

Pagination in SQL using ROW_NUMBER function
Pagination result – Page 1 shows first 10 records

In the above script, change the value of the variable ‘@PageNo’ to view various pages and the value of the variable ‘@RecordsPerPage’ to show the records.

DECLARE @PageNo INT = 3
DECLARE @RowsPerPage INT = 15 --Records Per Page

See the result below after changing the variable values

Pagination in SQL using ROW_NUMBER function
Pagination result – Page 3 shows 15 records

Pagination using OFFSET FETCH clause

Another method to implement pagination in SQL server version 2012 or above is by using OFFSET FETCH clause. The Syntax of the OFFSET FETCH clause is 

SELECT columnName(s)
FROM tableName
ORDER BY columnName
OFFSET Rows_To_Skip
FETCH NEXT Rows_To_Fetch ROWS ONLY;

The below query populates the first page with first 10 records.

DECLARE @PageNo INT = 1
DECLARE @RowsPerPage INT = 10 --Records Per Page
DECLARE @RowsToSkip INT  =  (@PageNo -1) * @RowsPerPage

SELECT * FROM Course 
ORDER BY department, id 
OFFSET  @RowsToSkip ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;

And the result is,

Pagination in SQL using OFFSET FETCH clause
Pagination result using OFFSET FETCH clause

Among the above mentioned methods, it is recommended to use the OFFSET FETCH method to implement pagination in SQL server 2012 and above for better performance.

Also, check “Generate comma-separated string from a table“