July 05, 2022
 
Paging Data in SQL Server
Minimize

Prior to the release of SQL Server 2005, retrieving paged data from the database involved some fairly ugly SQL. For example, to retrieve records 21 through 30, you might see something like this:

SELECT TOP 30 *
INTO #Temp_Table_1
FROM Table
ORDER BY SortField

Followed by:

SELECT TOP 10 *
INTO #Temp_Table_2
FROM #Temp_Table_1
ORDER BY SortField DESC

Finally followed by:

SELECT *
FROM #Temp_Table_2
ORDER BY SortField

One problem with this method include the additional disk I/O required to populate the temp tables when RAM alone cannot service the request. Consider the above queries when retrieving page 1538; obviously, the temp table requirements are substantially larger than when retrieving page 1.

Another method that is often used is for the business object to retrieve all the records and store them in an array. This provides faster access and easier paging (by using array indexing,) but again suffers when the resultset is large. You would also have to contend with stale data in a multi-user environment.

The ROW_NUMBER() function introduced with SQL Server 2005 makes paging data much simpler. Using WITH and ROW_NUMBER(), you can write the query like this:

WITH Temp_Table AS
(
    SELECT 'RowNumber' = ROW_NUMBER() OVER (ORDER BY SortField), *
    FROM Table
)
SELECT *
FROM Temp_Table
WHERE RowNumber >= 21
AND RowNumber <= 30

What is this query doing? The WITH clause behaves like a temp table, creating a resultset for you to work with. The ROW_NUMBER() call is fairly self-explanitory--it assigns each row in the "temp table" a unique row number. Along with the ROW_NUMBER() call, you can provide an ORDER BY clause to determine which order the row numbers should be assigned in. The OVER clause provides the ROW_NUMBER() function with that order. After the "temp table" is created, you can do a simple select from it and use the newly created RowNumber field to restrich the results to a particular page.

 Putting it to use...

The following stored procedure is one we use in the GyrumArticles DotNetNuke Module to retrieve one page worth of articles. The page size is configurable by the portal administrator and gets passed in to the stored procedure with the requested page number.

CREATE PROCEDURE [dbo].[GyrumArticles_GetArticlesByPage]
   @ModuleId int,
   @PageSize int,
   @PageNumber int
AS
WITH TempGyrumArticles AS
(
    SELECT
  'RowNumber' = ROW_NUMBER() OVER (ORDER BY CreatedDate DESC),
  *
    FROM GyrumArticles
 WHERE ModuleId = @ModuleId
)
SELECT *
FROM TempGyrumArticles
WHERE RowNumber >= (((@PageNumber - 1) * @PageSize) + 1)
AND RowNumber <= (((@PageNumber - 1) * @PageSize) + @PageSize)
ORDER BY CreatedDate DESC

Print