That's too bad. With SQL Server 2005 you can do this: With Books AS (select isbn, title, ROW_NUMBER() OVER(order by Title) as RowNumber from book) select * from Books where RowNumber between 2 and 14
....and then just use a simple calculation for the starting and ending row numbers of the page you want to retrieve. This is how we perform efficient custom pagination for ASP.NET 2.0 GridView controls that use object data sources. The equivalent SQL Server 2000 techniques I've seen all populate temporary tables with the *entire* contents of the target table, then select only those needed from the temp table. Try *that* under load! Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# & ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com ----- Original Message ----- From: Jim Curran To: CF-Talk Sent: Monday, March 27, 2006 1:27 PM Subject: RE: DB Pagination Question No, MSSQL 2000.... -----Original Message----- From: Adam Churvis [mailto:[EMAIL PROTECTED] Sent: Monday, March 27, 2006 1:14 PM To: CF-Talk Subject: Re: DB Pagination Question Are you using SQL Server 2005? Respectfully, Adam Phillip Churvis Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding Committee Get advanced intensive Master-level training in C# & ASP.NET 2.0 for ColdFusion Developers at ProductivityEnhancement.com ----- Original Message ----- From: Jim Curran To: CF-Talk Sent: Monday, March 27, 2006 10:45 AM Subject: DB Pagination Question Hi All, I am wondering what the best way to handle the following situation is. I have a database with approx 100,000 records. Users can search on these records with multiple criteria. Some result sets can contain 10,000 records or more. Also, there is a possibility of sorting the results. Using CF & SQL server, what is the most efficient way to paginate through the results, without hogging memory or re-running the entire query for each page view? TIA. - jim ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236265 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
