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 *
ORDER BY SortField
SELECT TOP 10 *
ORDER BY SortField DESC
Finally followed by:
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), *
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]
WITH TempGyrumArticles AS
'RowNumber' = ROW_NUMBER() OVER (ORDER BY CreatedDate DESC),
WHERE ModuleId = @ModuleId
WHERE RowNumber >= (((@PageNumber - 1) * @PageSize) + 1)
AND RowNumber <= (((@PageNumber - 1) * @PageSize) + @PageSize)
ORDER BY CreatedDate DESC