Note - I've never used MS-SQL, so what follows may be slightly off, but as MS-SQL is based on the Sybase code line it should still be relevant

Getting "pages" of result from an RDBMS is a popular question, and one that is essentialy a SQL question, really.
For MS-SQL and/or Sybase there is no direct way of only retrieving rows x,y from a result set. However, you can trick the server into doing it for you. For example, with Sybase I can write the following:

set rowcount $last_row
select tb.pk, id=identity(10) into #tmp
  from the_table tb
 where some condition
 order by something or other

set rowcount 0

select tb.pk, other columns
  from the_table tb
     , #tmp t
 where tb.pk = t.pk
   and t.id between $first_row and $last_row
[download]

This can be run as a single DBI prepare() call, and the second query will return the rows from the result set that are positioned between $first_row and $last_row.

The caveats with this technique is that as you page forward (or back) the results that you display may change if the underlying data changes. The initial set rowcount $last_row will limit the number of rows that are pulled from the base table and stuffed into the temporary table - usefull if the base table has a lot of rows that match the query.

An alternative solution is to fetch a large subset (say the first 10 pages worth) of primary keys and cache them locally. Then as the user pages forward (or back) issue a query with an IN clause with the primary keys for that set of rows.

As for the rows() not returning the correct (in your opinion :-) data - well that's documented. MS-SQL, Oracle, Sybase (and others) can't provide you with the count of the number of rows affected by a SELECT query until all the rows have been fetched. This is because the server doesn't know how many rows it has processed until it has processed all the rows (and finding out would take more time, and might not be correct in a multiuser environment anyway)

Michael

edited: to fix typo - thanks Abigail-II!