Flatliner wrote:Too bad if you have a 100 page result set with 100 rows per page. then selecting the last page would create the worlds largest execution plan ever devised.
well maybe. If you look at the execution plan for simple cases at least ( like the example ), the query essentially resolves to a single right anti-semi join, which can be resolved in a single I/O read pass over the data. This makes sense if you think about what you are really asking - scan past the first n rows, and then return me the next m. Of course you need to get your indexes right and like you say every case is different, but i have seen order of magnitude improvements in commercial applications with this approach...
Try sticking 1/2 million rows in the table and see how quickly it gives you back rows 300,000 to 300,050 say...
Can't argue with you about the dynamic sql
...Only good thing is that the inputs are integers and cant easily be exploited by sql injection attacks etc.
