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.

Now to list all the employees, the query is simple

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.

Now to get the first 10 Employees run the below query

To get the Employee List for 10th page,

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.

Now to get the first 10 Employees run the below query

To get the Employee List for 10th page,

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.

Now to get the first 10 Employees run the below query

To get the Employee List for 10th page,