Pagination in SQL Server

T-Sql
Fadıl
Pagination in SQL Server

Many times we have to build reports which list customers/employees/orders etc in the system.

At times when the systems are very large, the listing runs into several pages. Hence we need to support pagination in the reports.

There are several ways to support pagination using SQL Server. In this article, we will go through some of the methods for pagination.

To explain the various methods available, we will use the following table.

-- Create a table
CREATE TABLE EMPLOPLEE_MASTER
(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(100)
)
-- Populate some data
INSERT INTO EMPLOPLEE_MASTER (EmployeeName)
SELECT 'Employee ' + CAST(Number AS VARCHAR)
FROM master..spt_values WHERE type = 'p' AND number BETWEEN 1 and 10000

Now to list all the employees, the query is simple

SELECT EmployeeID,EmployeeName
FROM EMPLOPLEE_MASTER

Now we will go through the various pagination methods.

Method 1. Using temp tables with an identity column.

Here we first create a temp table which will have all the required columns and an additional IDENTITY column identifying each row in a specific order as required in the output.

This method can be used in any version of SQL Server.

CREATE PROC Get_Employee_List_Method1_Using_Temp_Table
@pageNo INT
,@rowCnt INT
AS
BEGIN
IF OBJECT_ID('tempdb..#TEMP_EMPLOYEE_LIST') IS NOT NULL
DROP TABLE #TEMP_EMPLOYEE_LIST
CREATE TABLE #TEMP_EMPLOYEE_LIST
(
rowno INT IDENTITY,
EmployeeID INT ,
EmployeeName VARCHAR(100)
)
INSERT INTO #TEMP_EMPLOYEE_LIST(EmployeeID,EmployeeName)
SELECT EmployeeID,EmployeeName
FROM EMPLOPLEE_MASTER
ORDER BY EmployeeID
SELECT EmployeeID,EmployeeName
FROM #TEMP_EMPLOYEE_LIST
WHERE rowno BETWEEN (@pageNo-1)*@rowCnt+1 AND @pageNo*@rowCnt
END
GO

Now to get the first 10 Employees run the below query

EXEC Get_Employee_List_Method1_Using_Temp_Table @pageNo = 1, @rowCnt = 10

To get the Employee List for 10th page,

EXEC Get_Employee_List_Method1_Using_Temp_Table @pageNo = 10, @rowCnt = 10

Method 2: Using Ranking Functions
From SQL Server 2005 onwards, we have built in ranking functions such as ROW_NUMBER(), RANK(), etc.

In this method, we will use the ROW_NUMBER() function to create pagination.

CREATE PROC Get_Employee_List_Method2_Using_Ranking_Function
@pageNo INT
,@rowCnt INT
AS
BEGIN
;WITH CTE
AS
(
SELECT EmployeeID,EmployeeName,ROW_NUMBER() OVER( ORDER BY EmployeeID) as rowno
FROM EMPLOPLEE_MASTER
)
SELECT EmployeeID,EmployeeName
FROM CTE
WHERE rowno BETWEEN (@pageNo-1)*@rowCnt+1 AND @pageNo*@rowCnt
END
GO

Now to get the first 10 Employees run the below query

EXEC Get_Employee_List_Method2_Using_Ranking_Function @pageNo = 1, @rowCnt =10

To get the Employee List for 10th page,

EXEC Get_Employee_List_Method2_Using_Ranking_Function @pageNo = 10, @rowCnt = 10

Method 3: Using OFFSET/FETCH
Note: Below method is using a feature of an unreleased version of SQL Server. So Depending on whether the feature goes live or changes in the implementation, the solution may or may not work.

SQL Server Denali (CTP1) introduced new functionality OFFSET/FETCH which can be used in conjunction to ORDER BY clause to implement pagination.

CREATE PROC Get_Employee_List_Method3_Using_OFFSET_FECTH
@pageNo INT
,@rowCnt INT
AS
BEGIN
SELECT EmployeeID,EmployeeName
FROM EMPLOPLEE_MASTER
ORDER BY EmployeeID
OFFSET ((@pageNo-1)*@rowCnt) ROWS
FETCH NEXT (@rowCnt) ROWS ONLY;
END
GO

Now to get the first 10 Employees run the below query

EXEC Get_Employee_List_Method3_Using_OFFSET_FECTH @pageNo = 1, @rowCnt = 10

To get the Employee List for 10th page,

EXEC Get_Employee_List_Method3_Using_OFFSET_FECTH @pageNo = 10, @rowCnt = 10