Paging Data with TSQL

When building applications that need to display lots of data to a customer, a common practice is to display one page of data at a time.  Having your application only show a single page of data requires you to build a paging function, like a button into your application to allow the users see the next page of data.   When your application requires that you display only one page of data at a time a common practice is to build a SELECT statement that only returns enough data to fill a single page, then if the user decides to page down you can run another SELECT statement to bring back the next set of records.   There are a number of ways to provide paging functionality.  In this article I will discuss a number of different TSQL methods I have run across to return a specific number of rows each time a SELECT statement is run.

Creating Sample Data for Examples

Before I can show you all the different methods of writing a SELECT statement to page data, I first need to establish some test data.  This test data will be used for all of my examples.  If you want to follow along and run all my examples then run the code that follows to create the sample dbo.Person table:

USE tempdb;
GO
CREATE TABLE dbo.Person (ID int identity,
                     FirstName varchar(20),
                                   MiddleInitial char(1),
                                   LastName varchar(20));
INSERT INTO Person VALUES
       ('John','J','Jackson'),    
       ('Mary','F','Smith'),      
       ('Sue','U','Jones'),
       ('Bob','P','Thomas'),      
       ('Alan','T','Johnson'),    
       ('Joshua','A','Adams'),    
       ('Danielle','E','Jefferson'),     
       ('Martin','B','Shelton'),  
       ('Molly','N','Buster'),    
       ('Sheri','C','Horton'),    
       ('Sherwood','D','Abraham'),
       ('Randy','J','Morris');

In this example I create and populate my Person table in the tempdb database.  My table is populated with 12 different names.  Each one of my examples will show you how to write some TSQL code to process through this sample data returning four rows at a time.

How to Page Data Using a Temporary Table

An approach to paging data that has been around for a very long time is to use a temporary table.   Here is my code that uses a temporary table to provide paging functionality:

USE tempdb;
GO
CREATE TABLE #Person (RowID INT IDENTITY(1,1),
                      FirstName varchar(20),
                                    MiddleInitial char(1),
                                    LastName VARCHAR(20));
  
INSERT INTO #Person (FirstName, MiddleInitial, LastName)
SELECT FirstName
      ,MiddleInitial
         ,LastName 
FROM Person 
ORDER BY LastName, FirstName, MiddleInitial;
 
DECLARE @FirstID INT;
DECLARE @Max INT;
SET @FirstID = 0;
SELECT @Max = COUNT(*)  FROM dbo.Person;
 
WHILE @Max >= @FirstID + 4
BEGIN
       SELECT Firstname
                ,MiddleInitial
                ,LastName 
       FROM #Person
       WHERE RowID > @FirstID AND RowID <= @FirstID + 4
       SET @FirstID = @FirstID + 4
END

In this code I first create a temporary table named #Person.   This table is then populated with all the columns and rows from my original dbo.Person table, plus an additional column named RowID. The RowID column is defined as an identity column.  I populated the columns in this temporary table using a SELECT statement with an ORDER BY clause, so the ROWID column would be populated in sequential order.   This way the ROWID column can be used to return the rows in the order in which I want to display them.  Once my temporary table was populated I went through the WHILE loop.  In the WHILE loop I ran a SELECT statement that contains a WHERE clause that uses the RowID column of my temporary table and the @FirstID variable, to control returning four records, with every pass through the WHILE loop.  With each pass through the WHILE loop I incremented the @FirstID variable by four so the next pass through the loop would pick up the next set of four names. 

Using TOP the Clause

Another method of returning a small set of records that can be used to page data is using the TOP clause to control the number of records returned.  Below is an example that uses the TOP clause to identify the number of rows to return:

USE tempdb;
GO
DECLARE @LastRowName varchar(20);
DECLARE @LoopCountMax int;
DECLARE @CurrentLoopCount int;
SET @LastRowName = '';
SELECT @LoopCountMax = COUNT(*) / 4 FROM Person;
SET @CurrentLoopCount = 0;
WHILE @CurrentLoopCount < @LoopCountMax
BEGIN  
       SET @CurrentLoopCount = @CurrentLoopCount + 1;
       SELECT TOP 4 FirstName
                      ,MiddleInitial
                      ,LastName
       FROM Person
       WHERE LastName + FirstName + MiddleInitial > @LastRowName
       ORDER BY LastName, FirstName, MiddleInitial
       SELECT TOP 4 @LastRowName = LastName + FirstName + MiddleInitial
       FROM Person
       WHERE LastName + FirstName + MiddleInitial > @LastRowName
       ORDER BY LastName, FirstName, MiddleInitial
END    

In this example I first calculate how many times I will need to go through my while loop by dividing the number of records in my table by four to determine the variable value @LoopCountMax.  Just like in the last example, the first SELECT statement in the WHILE loop returns four rows with each pass through the loop.  The second SELECT statement in the WHILE loop sets the variable @LastRowName to the value of the name fields on the last row that was returned from the SELECT statement.  This variable is used to control where to start retrieving the set of rows for each pass through the WHILE loop.

How to Page Data Using ROW_NUMBER Function

With the introduction of the ROW_NUMBER function in SQL Server 2005 you can dynamically generate a row number value that can be used to page through your data, instead of having to generate a temporary table like my first example.  The code below shows how to use the ROW_NUMBER function to return a new set of four records with each pass through the WHILE loop:

USE tempdb;
GO
 
DECLARE @FirstID INT;
DECLARE @Max INT;
SET @FirstID = 0;
SELECT @Max = COUNT(*)  FROM dbo.Person;
 
WHILE @Max >= @FirstID + 4
BEGIN
       SELECT Firstname
                ,MiddleInitial
                ,LastName 
       FROM (SELECT FirstName
                   ,MiddleInitial
                           ,LastName
                   ,ROW_NUMBER() OVER (ORDER BY LastName, FirstName, MiddleInitial) AS RowNumber
      FROM Person) P
   WHERE RowNumber > @FirstID AND RowNumber <= @FirstID + 4;
   SET @FirstID = @FirstID + 4;
END

In the example above I used the ROW_NUMBER function in a sub query within the FROM clause of my SELECT statement.  I then used the generated RowNumber column value from the set returned from the sub query in the WHERE clause to identify which four rows I wanted to return with each pass through the WHILE loop.    Once again I control the rows returned by incrementing the @FirstID value by four each time through the loop.

How to Page Data Using a CTE

With the introduction of Common Table Expression (CTE) in SQL Server 2005 you have another option for paging through a record set.  The following example shows you how to use a ROW_NUMBER function within a CTE to page through data:

USE tempdb;
GO
DECLARE @FirstID INT;
DECLARE @Max INT;
SET @FirstID = 0;
SELECT @Max = COUNT(*)  FROM dbo.Person;
WHILE @Max >= @FirstID + 4
BEGIN  
       WITH EmployeePage AS
       (SELECT FirstName
       ,MiddleInitial
          ,LastName
          ,ROW_NUMBER() OVER (ORDER BY LastName, FirstName, MiddleInitial) AS RowNumber
       FROM Person)
       SELECT FirstName
                ,MiddleInitial
                ,LastName
       FROM EmployeePage
       WHERE RowNumber > @FirstID AND RowNumber <= @FirstID + 4;
   SET @FirstID = @FirstID + 4;
END

This example is very similar to my prior example.  The only difference is I replaced my sub query within a CTE.

How to Page Data Using  Offset_fetch Options

With the introduction of SQL Server 2012 Microsoft provides you yet another method of paging through your data. This next example uses the offset_fetch option of the ORDER BY clause to page through a record set:

USE tempdb;
GO
DECLARE @FirstID INT;
DECLARE @Max INT;
SET @FirstID = 0;
SELECT @Max = COUNT(*)  FROM dbo.Person;
WHILE @Max >= @FirstID + 4
BEGIN  
       SELECT FirstName
                ,MiddleInitial
                ,LastName
       FROM Person
       ORDER BY LastName, FirstName, MiddleInitial
       OFFSET @FirstID ROWS
       FETCH NEXT 4 ROWS ONLY;
       SET @FirstID = @FirstID + 4;
END    

Here I have once again written a WHILE loop that process through my record set, four records at a time.  But this time, for each iteration through the loop, I use the “FETCH NEXT” clause to return only four rows at a time, based on the starting position, which is set by the “OFFSET” clause.  The OFFSET clause uses the @FirstID variable to control where to start bringing back four records.  I then add 4 to variable @FirstID so each time through the WHILE loop the next set of four records are returned.  

Using Different Methods

Whenever you have options for how to code some logic you need to determine which option is best for your situation.  The decision as to which paging method will be used might be dictated by the version of SQL Server you are running.  If not then you need to decide which method makes the most sense for you when selection is an option.  Things to consider are performance, ease of reading and/or maintenance of the code and any other criteria that you think is important.  Now if your application requires you to page through a specific number of rows at a time, you have a number of options you can pick from to provide that functionality.

See all articles by Greg Larsen