Unanswered: How to do paging when there are over 80 000 rows in table?
I have one table in my db which contains some 80 000 rows of data. I want to list the rows in a web page so that one page has 200 rows. I also want to put the first,previous,next and last buttons into the web page so that the users can navigate between the pages/rows nicely.
How can i do this with SQL-Server? I dont want to read all the rows because it takes too long (20 seconds) and uses too much memory.
Do i have to do it like this?
SELECT TOP 200 * FROM TABLE WHERE ID>0 (First page)
SELECT TOP 200 * FROM TABLE WHERE ID>200 (Second page)
SELECT TOP 200 * FROM TABLE WHERE ID>400 (Third page)
.... AND SO ON ....
ID = Primary key field (identity insert 1, +1)
(Of course the ID values are different than 0,200,400,.... if there are deleted rows in a table.)
Only 20 seconds for 80,000 rows of data in a web page - not bad. If you can use a query like select top 200 * from table where id > ?, then use a stored procedure with n as a parameter and use that to multiply by 200. As you progress through the web site, for next just add 1 and for previous subtract 1 from the value you submit to the stored procedure. You could use 1 asp page to handle this functionality. Normally, you are not this lucky and have to use remote scripting to handle large recordsets to return x rows to a web page.