See Re: Get next n records from previous call Thread #691967 and http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=8785

>Consider a SQL Server table sitting on a Website with say 1,000,000 records. The user on the internet wants to be able to see the first 100 records (sorted by ProductID), then click "Next" and see the next 100 and ... so on.
>How to I write a query to retrieve the nth set of these records.
>ie the query should retrieve the 100*(n-1) to 100*n set of records in the order of field ProductID when the user clicks "Next" for the nth time.