Suppose I have a query with a "complex" filtering condition
<complex condition on non-indexed fields>
Now further suppose the database is very large (as in hundredthousands of records)
And this query is part of a webservice (which is what I'm writing) used from a third party website (which I'm not).
In order to limit the time the DB is busy processing the request, and because I don't want to return potentially thousands of records at once, I want to retrieve a set number of records at a time which would be presented on the website one page at a time with previous/next buttons.
I know I can restrict the sql query with say "TOP 10", but what would be a good way to do the next page (11 to 20) and the page after (21 to 30) etc. in such a way that the DB doesn't have to restart the entire query over from the start. or in other words, what's the "optimal way" to continue a previous (set of) TOP X