mssql pagination

I can't imagine I'm the first person to try to do something like this, so hopefully you guys can give me some insight!  I know this isn't strictly CF-related -- but I'm hoping some of you have tried to do something like this in the past. I'm trying to produce a paginated list of records.  The query I'm working with has about 20000 rows, and I'd rather not force the user to sift through all those pages in a single page request.  So I'd like to break it up, maybe display 50 records per page.  I'd also prefer not to have the full query sitting in memory every request.  If I'm only displaying rows 50-100, I only want rows 50-100 in the query object.  Using cfloop with startrow and maxrows won't cut it this time. Apparently this is super easy in MYSQL using the LIMIT (records), (offset) clause. Turns in MSSQL makes things difficult.  A couple sites I hit up showed examples of using of subqueries and the TOP filter, or (ugh) temporary tables.  I went with the subquery/TOP approach.  For example, ordering by dateAdded and wanting to show rows 20-30: SELECT TOP 10 sampleID FROM Samples WHERE 0=0 AND sampleID NOT IN (   SELECT TOP 20 sampleID   FROM Samples   ORDER BY Samples.dateAdded ) ORDER BY Samples.dateAdded "Grab the first 10 records after eliminating the first 20 records, always sorting by dateAdded." But I found it worked sporadically.  Pages 1 and 2 worked fine, displaying the 1-10 and 11-20 records respectively.  But page 3 gave me some strange results.  It showed records 14-23 or some other mismatch.  Obviously not what I was looking for! After some investigation, I found out my subquery was not returning the right samples to exlude.  What I haven't been able to figure out is *why* they are not returning the right subset.  As far as I can tell, the SQL looks good to me. So my question to you guys is -- 1.  Can you see anything wrong with how I've set up my Query? 2.  How have you dealt with pagination in the past?  Is there an easier route than what I'm taking now? PS -- I am running on MSSQL Server 2000 (I have heard they offer an OFFSET clause in 2005, but sadly, I do not have access to that). If anyone is interested in seeing some sample data of what I'm working on, I put up this HTML page: http://www.vimm.com/developerTools/sampledata.html You can see in the "All Samples" section that records 20-30 should be: [147, 148, 149, 150, 63, 36, 40, 382, 383, 384].   But the Total Wrapper query returns: [146, 147, 148, 149, 150, 63, 382, 383, 384, 440]. What happened to record 40?  It's row #27 in the All Samples query, but it's included in the exclude Nested Subquery that should only be pulling the top 20 results.  How does that happen? Jonathon

It's easy in SQL 2005, and very scalable for large rowsets: WITH PagedResultSet AS (   SELECT     ROW_NUMBER() OVER(ORDER BY LastName ASC, FirstName ASC) AS RowNumber,     UserID,     Email,     LastName,     FirstName   FROM     ApplicationUser ) SELECT RowNumber, UserID, Email, LastName + ', ' + FirstName AS FullName FROM PagedResultSet WHERE RowNumber BETWEEN   <cfqueryparam value="#rangeStart#" cfsqltype="CF_SQL_INTEGER"> AND   <cfqueryparam value="#rangeEnd#" cfsqltype="CF_SQL_INTEGER"> ORDER BY LastName ASC, FirstName ASC This is *much* better than retrieving everything and letting it sit in memory for a number of reasons. Hope this helps. Respectfully, Adam Phillip Churvis President Productivity Enhancement ----- Excess quoted text cut - see Original Post for more -----

> It's easy in SQL 2005, and very scalable for large rowsets: <snip> I know you're running on 2000... just bite the bullet and upgrade to 2005. It's already 2008, and SQL Server 2008 is almost here.  2000 is more than a bit long in the tooth. Respectfully, Adam Phillip Churvis President Productivity Enhancement

Jonathon, Here's a stored procedure you can use in MSSQL 2k for pagination: http://blog.pengoworks.com/index.cfm/2006/6/19/MSSQL-2k-Stored-Procedure-for -Pagination It doesn't use #temp tables and it will only return the pages of data you're after. I've used it on tables with several millions of records and had great success with it. Also, in SQL Server 2005 you can solve this problem with Common Table Expressions (CTE) -Dan ----- Excess quoted text cut - see Original Post for more -----

Thanks Dan, Adam, and Eric!   I haven't played around with stored procedures much, so this will give me a chance to give them a whirl. As for 2000 being out of date, I hear you there -- I'll have to push to get the budget for it.  At least now I have some leverage (a problem that can't easily be fixed otherwise). Jonathon

Jonath > As for 2000 being out of date, I hear you there -- I'll have to push to get > the budget for it.  At least now I have some leverage (a problem that can't > easily be fixed otherwise). How about "mainstream support ends this year"? http://support.microsoft.com/lifecycle/search/?sort=PN&alpha=sql&x=9&y=10 Jochem

That will definitely help my footing as well!   Arguably even more than my fringe case that I couldn't work around. ;) Thanks, Jochem! Jonathon

----- Excess quoted text cut - see Original Post for more ----- Since the sort order you are using (dateAdded) is not unique, it is probably using some other factor (like a clustered index or record insertion order) along with dateAdded to sort the records returned. Use something like this... SELECT TOP 10 sampleID, dateAdded FROM Samples a WHERE 0=0 AND a.sampleID NOT IN ( SELECT TOP 20 b.sampleID FROM Samples b ORDER BY b.dateAdded ASC,b.currentRow ) ORDER BY a.dateAdded ASC,a.currentRow; However, I'm guessing that the above will not scale well with a very large dataset, so something like the SP sent earlier may be a better solution.