- Posted by justin on February 8, 2007
SQL Server 2005 has a ROW_NUMBER Function that can help with paging records for you database applications. ROW_NUMBER returns a sequential number, starting at 1, for each row returned in a resultset.
If I want the first page of 10 records from my log file sorted by Date DESC, I can use the ROW_NUMBER FUNCTION as follows:
SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 1 AND Row <= 10
The second page of 10 records would then be as follows:
SELECT Description, Date
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 11 AND Row <= 20
If you have a lot of records, using TOP X in the inner SELECT clause may speed up things a bit as there is no use returning 1000 records if you are only going to grab records 11 through 20:
SELECT Description, Date
FROM (SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Description, Date FROM LOG)
AS LogWithRowNumbers
WHERE Row >= 11 AND Row <= 20
We can rap this up in a Stored Procedure as follows:
CREATE PROCEDURE dbo.ShowLog
@PageIndex INT,
@PageSize INT
AS
BEGIN
WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)
SELECT Date, Description
FROM LogEntries
WHERE Row between (@PageIndex - 1) * @PageSize + 1
and @PageIndex*@PageSize
END
source: http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx
Another Article on the same topic from Scott Mitchell (http://feeds.feedburner.com/ScottOnWriting?m=86)
My two latest articles on 4Guys examine how to efficiently page through very large resultsets in ASP.NET 2.0 using SQL Server 2005's new ROW_NUMBER() keyword. While default paging is as simple as point-and-click in ASP.NET 2.0, it's horribly inefficient and naive, bringing back all records from the underlying data source for each page of data displayed. Custom paging, on the other hand, allows you, the developer, to write code/stored procedures that will intelligently bring back the right subset of records.
The performance difference between these two techniques can be profound for sufficiently large resultsets. As I detail in Custom Paging in ASP.NET 2.0 with SQL Server 2005, paging through a 50,000 record table using default paging was nearly two orders of magnitude less efficient than custom paging. One challenge with custom paging, though, is that adding the boilerplate bi-directional sorting functionality is no longer a point-and-click proposition. Fortunately, it's not terribly difficult to implement, although there are a few subtle points that, if missed or overlooked, can impact performance. In Sorting Custom Paged Results I show how to efficiently include bi-directional sorting with custom paging - all it takes is a bit of dynamic SQL in your stored procedure and indexes on the columns that can be sorted.
Implementing custom paging in ASP.NET 2.0 is a good deal more involved than implementing default paging (as was the case in 1.x), but, in my opinion, custom paging in 2.0 is much easier to implement and get working right than in 1.x. (Ditto for default paging in 2.0 vs. default paging in 1.x.)
The whole custom paging vs. default paging brings up a good question: do you use custom paging all the time? That is, whenever creating a data interface using a GridView (or DataGrid), do you always use custom paging, or do you only implement custom paging if you know the resultset will contain hundreds or thousands of records? Personally, I do default paging if there are going to be less than 100 records in the resultset because of the ease of implementing default paging vs. custom paging. This can be a dangerous tactic, though, because even when you might expect that a resultset will remain relatively small, unless the resultset is something like the months of the year or the states in the US, there's no real guarantee that the size won't later swell into something less manageable. ... food for thought!