"Recordset" Paging in Sql Server
Posted on 2004-09-17
In a three tiered app we use the middle tier to implement recordset paging using standard ADO recordset positioning.
Each user can see 200 rows at a time, and they have First, Next, Prev, Last buttons to move to the next set of rows.
The problem is that if we select 1,000,000 rows the whole lot comes down to the application server.
Therefore I want to implement paging on the sql server instead. I don't mind running the query again, its the bandwidth on the resultset thats the problem.
I know we can do
select top 200 ......
but how can we do
select mid(startrow, numberofrows) .....