Our reports need to be live and quick - don't they all? The overwhelming majority of tables in our application database just have INSERTs - and the tables have incrementing primary keys. Our tables are quite large but our daily INSERTs are relatively few. ie a table may have over 35,000 rows but only 400 INSERTs a day.
The data structures off which our reports are written are views into the live application database (technically a replicated copy).
What would be swell is to, effectively, make copies of these views every night and save those copies in the reporting database. Then have SQL Server magically determine if the table and/or the view is required to handle the reporting query. So if the query is looking at last year, it uses the table - if it's the last hour, it uses today.
It is fairly easy for me to write a view that unions the table and the "today data" that is not in the table, thanks to the incrementing PKs. But the query plan shows the view is always run, even when it's not required.
I hope that's enough detail to highlight what I am trying to accomplish. Is this possible in SQL Server? Any other creative ideas to handle this?
Thanks!!