Not sure if you've seen this before, but here is an article describing a technique that SIGNIFICANTLY improves paging performance (see the performance comparison table at the bottom of the article). http://www.4guysfromrolla.com/webtech/042606-1.shtml

Referenced from this blog entry: http://scottonwriting.net/sowblog/posts/5992.aspx

Thanks for the nice article.

THe rowcount trick doesn't work in a lot of situations, hence the reason it's not used in the runtime. I've tried every trick there is and the temptable was the only one which was reliable.

The temptable approach is actually pretty fast, though it depends on how you've setup the tempdb in sqlserver. Some people keep the tempdb very small, which causes re-sizing at runtime and thus slow queries.

In v2, you can switch to CTE paging on sqlserver 2005, which is the fastest way of doing paging on sqlserver.