Paging large result sets

I use SqlServer on my web app written in Java. I have went through quite a big effort in trying to realize a flexible paging algorithm that I can include in my set of libraries. However having to deal with too many unknows like do I want the RS ascending/descending, what is the first/last itemid, next/previous page (and then invert logic what is up what is down) I'm quickly going mand. I realise there is a fine line where this is a tsql problem (like, do I use TOP x or not, how do I create all varying parameters on which I'd like to filter on etc.) and architecting java code with DAO's and a matter of how to put all of this together, so in order to avoid this, can you suggest how should a general SQL query be to allow this kind of behaviour? I know that stored procedures did not allow TOP $var in SqlServer2k is this still the case? Should SQL allow for constructs that would allow easy paging?Hello!

Yes, paging is a missed feature in SQL Server and it is still missed with version 2005. I do not understand why MS does not work on a construct to allow querying a sliced result. although, there is a known possibility for efficient server side paging, working with some millions of records, if adequate indexes are existent. This solution is based on nested "select top x" statements. You can find a good description at ....

http://weblogs.asp.net/pwilson/archive/2003/10/10/31456.aspx
But i prefer my one extended version which additionally is based on a primary key, which allways allows me to find the exact next or previous record.

Regards,
Tom|||Hi,
I like this stored procedure, although there can be infinite solutions to this problem I've marked it as a correct answer. It even addresses the problem that you can't pass the TOP x where x is a parameter of the procedure. Even the comments are insightfull.
Good work ;)

PS. I somehow wonder if the Command and Builder pattern might be used as well on the server side. Any ideas or links?|||

There are lot of problems with the stored procedure from the link above. Here are some of the issues:

1. It doesn't protect you from SQL injection attack
2. Use of EXEC instead of sp_executesql. The later can produce cacheable plans for the dynamic SQL statement
3. SP returns multiple resultsets which requires more work from client-side to handle. It is best to avoid it unless necessary

Having said this, the stored procedure does demonstrate one technique to page resultsets in SQL Server 2000. And if you incorporate such technique, please make sure to protect against the problems mentioned above.

You may also want to consider avoiding paging of resultsets in the GUI. You can provide like a search and locate type of functionality which will result in less number of rows being pulled from the server to client. This might also provide a better user experience than going through 100 rows at a time to find the one of interest. These type of paging techniques typically produce more load on the server since you are processing more rows in every query to locate the ones of interest. In any case, if you still have a requirement to do paging of the resultset then a dynamic query using TOP is the best way to go in SQL Server 2000.

In SQL Server 2005, you can also use the ROW_NUMBER() function to do this slightly more efficiently. ROW_NUMBER function allows you to generate a sequential number for the each row in a resultset and you can apply filters on it to perform the paging. We are working on a white paper that will compare the various paging techniques that should appear in the Microsoft SQL Server and MSDN web sites.