This is an example of how to Cache Query results on the Server side
then page though the results
!!!This process should be reserved for queries that normally take a long time
where running the base query for each page results in a unacceptable time lag between pages!!
This is for use with Mobile devices to avoid pulling large or complex data sets
across the connection and trying to cache them on the device then page
This Caches the complex query results the first pass, avoiding running the complex query
each time you page. Once cached, you can page through the simple cached table to return
the results to the client one page at a time, avoiding the client side caching.
The line count and page count of your result set are returned with each query so you can keep track
of your position in the dataset
Final pass, you can (and should) destroy the table after use.
By using TempDB you avoid the orphaned table problem, if the client disconnects unexpectedly,
as TempDB is cleaned up each time SQL is restarted.