Had to recheck the date of this post. People who use AOL? In 2014?

Yes I am convinced. I can see it now: A bunch of execs at a board meeting firing up SSMS and just querying happily away :)

This helps indeed. More and more I am getting convinced that separate DB with permanent tables is the way to go. Thanks guys you're the best.

User may have dozes of different criteria he/she would want to run ad-hoc. Those criteria aren't know in advance and need to be passed to data on the fly.

Yup, looks like this is the way we're going to take as well. Thanks

Again, the only condition they know in advance is "last 3 months". It is passed to SP - which is expensive to run, it may take several minutes (and please don't ask me to optimize it, it's not an option at the moment). Once the data is there - it needs to present a flexible, dashboard view. User clicks the Name column to filters data, observes result, uses it elsewhere, removes the filter. User searches for SSN that ends in 74, and uses that data, removes the filter. User sorts data by date, jumps to the 1st page, jumps to the last page. Again, these requests cannot be passed to SP - it will take too long, they need to be done to subset of data, returned by SP (yes those several million of records) that presumably are stored in some local storage.

That's the point, I don't know in advance what filters are going to be used; I can only restrict the data somewhat by initial call too the SP; but I cannot call SP for every filter request - this call is to expensive. For example: user needs to work with data for the past 3 month - this Date filter I can pass to SP and this call can returns several million of records. Once the data is obtained - user may want to: Get only rows which "Name" field start with "D" or which SSN ends with 74 or group by LastName and get the count etc. etc. - these requests will limit data to a much smaller subset, but are unpredictable and made to the original large resultset returned by the SP - instead of SP itself

I apologize, I must've not made my intentions clear. User won't need to see all those few million records at once. He/she would need to make ad-hoc request to this dataset: filter/sort/search it on the fly and expect results fast. Thanks for the math lesson though :)

Yup, I think this is what it's shaping out to be: A dedicated DB that'd hold temp permanent tables with cached data. Thanks!

I am coming to this conclusion myself, but normal #temp tables won't persist between connections, if I am to use ##global or permanent tables I'd need to know when to drop them - and I am not sure about that yet.

Not exactly. These methods assume that data is stored in a table. In my case to retrieve the data an expensive SP is run, and *after* that user needs to do filtering/paging/sorting on that data. These ad-hoc requests are made from ASP.NET code and needs to be done pretty fast. Do I need to store resultset in an actual table (#temp tables won't work in this scenario) or are there other ways?

I have a TSQL stored procedure that can run for a few minutes and return a few million records, I need to display that data in an ASP.NET Grid (Infragistics WebDataGrid to be precise). Obviously I don't want return all data at once and need to setup some kind of paging options - every time user selects another page - another portion of data is loaded from the DB. But I can't run the SP every time new page is requested - it would take too much time. What would be the best way to persist data from the SP, so when user selects a new page - new data portion would be loaded by a simple SELECT... WHERE from that temp data storage?