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.