Paging large Results in SQL 2005

lets say we have more than 100 000 rows in Table1, and we want to view each 10 rows alone... and by pressing on a NEXT button we will see the other 10 pages...

there is 2 buttons : NEXT and PREVIOUS

so can anyone tell me how to do that in SQL 2005, and what is correctly called.

I have found a code that does use ROW_NUMBER in order to view results between 2 numbers,

example: rows between 10 and 50...
but It is not what I want, so please I need some help, thank you

By Uncle SamIf you code this by program, you can use ADO object that contains "PageSize" property to set how many records could be shown in a page.|||you can do some trick with the NTILE function to get the result. though it wont perform good. also with NTILE function you need back calculate the number of pages if 10 rows per page is to be displayed....

select * from (select *,ntile(2) over(order by COL1) as PgNo from test) as TempTbl where TempTbl.PgNo = 2|||Ok can you tell me how to do that, because I am beginner|||+1 : bad idea to solve this issue using cursor or any other server-side trick. You must manage that in your client-side...|||ok but is there any Stored procedure that does it|||Order your result set by a primary (natural) key. Write your NEXT procedure to take a starting key and a requested record count and return that number of records starting at that point in the result set. Your application just needs to know that last pkey it received in order to request the next page. Similar logic works for PREVIOUS recordsets.|||okay but please can you write for me the code, because I am still a beginner and this is my project. Thank you so much|||Are you doing your project for free? Because I generally charge something for my services...

I encourage you to either hire a dba to help you, or learn advanced SQL real fast.|||sorry but I can't afford paying an advice|||You're not asking for advice. You are asking for somebody to do the work for you.