There is a rarely used feature of the system I maintain, that combines prospect/quote/order and archive data - and allows users to query it as a whole. It works by creating a temporary table of a union of the 4 data sources, then the user's query acts on the temporary table.
I say rarely used. When it is used, it is used a lot - but there may be weeks between uses.
What I want is to create this table once, if it hasn't been used recently and cache it, then continually query the cache table - and let it expire from the cache - maybe after 24 hours.
Is there any feature of SQL Server to accommodate this? - or a design pattern someone can refer me to?
Thanks
