More on SQL Paging
In the PDC build of Whidbey, the DbCommand class (the mother of all xxxCommand classes in data providers) has a method named ExecutePageReader with the following signature:
public SqlDataReader ExecutePageReader(
CommandBehavior behavior,
Int32 startRow,
Int32 pageSize);
I was curious to see the implementation of this method. Secretly, I hoped it was better than the one described here--a group of nested SELECT statements. Instead, it declares a server cursor on the query and moves it back and forward until you close the reader. Many SQL gurus (including Microsoft's SQL gurus) disagree with this approach. I don't know whys and whats but I believe that the low-level implementation of paging is still a debatable point.
(Which is what leads me to say that paging LARGE queries is a problem to avoid, rather than solve <g>)
PS: The good news is that ASP.NET data-bound controls (i.e., GridView) support paging as long as the data source control provides that capability. Which means that at least we're going to have forward-compatible controls.