best approach to paging ?

[Sol Mayer-Orn]

Would anyone happen to have a recommendation on how to perform paging ?
In particular, say you read pages out of a PRODUCT table, with 100 rows per page, and you need page 9.

Now, if it's Oracle, the following should work:

 "select * from product where rownum between 900 and 1000"

However, when using DB2 (or SQL Server), it's my understanding you can only do FETCH FIRST (or SELECT TOP), which means you need to get the first 1000 rows, and then deduce the fist 900.
In that case, what's the best way to go about it without hindering performance ? I've considered the following 2 methods, but any other recommendations would be welcome.

Method 1 - use more complex SQL:

select * from product 
where ID in (select id from product FETCH FIRST 1000)
   and NOT ID in (select id from product FETCH FIRST 900)

Method 2: perhaps it would be more efficient to fetch only the first 1000, and then programmatically extract the last 100:

- Run a "select * from product FETCH FIRST 1000"
- get it into a scrollable result set
- put cursor on line 901
- copy rows 901 to 1000  into a cached row set

However, is there a CachedRowSet implementation that allows to copy selected rows into it ? I've tried Sun's new CachedRowSetImpl, but got the impression you can only add data to it provided you add it simultaniously to the database. which is of course not required here.

Any solutions, or other approaches, would be very very welcome. However, i'd rather avoid stored procedures unless there's absolutely no other choice.

Thankx a lot.