How can I return only rows n to n+20?

[fmw1]

(MSSQL 7 or 2000)
I have a very large table that I want to read in a page at a time (about 20 rows). I've tried using PageSize in ADO, but this appears to want to pull back the entire recordset before passing me the 20 rows (the more rows in the dataset, the longer it takes to return the results).

I can use 'TOP n' to retrieve the first n rows, but I need to retrieve say 20 rows starting from row 101.

Is there any SQL to return such a chunk of rows?

 

[sunila7]

Hi,

Have a look at this thread on paging...

thread183-474213

Sunil

 

[fmw1]

Many thanks Sunila. Copying all 10000 keyfields to a temp table every time the user clicks PageDown seems a bit wasteful on resources, but I may be able further filter the table.

Alternatively, the links have given me the thought of using a cursor to fill a temp table, and just return that...

Thanks again.