jcraddock (3/11/2009)
These are all well and good, but I rarely need such results in a T-SQL window. Where I need them is in an application where I want to paginate using ASP.net etc.In those cases, the best I can come up with is two calls. To be clear - I use dynamic sql to build all queries in my list windows. There I need the total count and the page of data required.
This is exactly the scenario I was imagining when putting this solution together, with the idea being that you can return both the total count and page n in a single call/read.
There has been some very interesting comments and ideas in this thread. As far as this idea goes, it works well in some cases, and not so well in others (as highlighted by other posters).
I built a paging proc based on this principle and, after extensive testing, found it to be the best approach. I saw only a few ms of cpu & elapsed time overhead. I would be very interested to know the size of some of the datasets peso and others are using. This would be very useful in determining the best solution for a given problem.
Thank you, everyone, for you comments and insight!
SQL guy and Houston Magician