We have a big de-normalised warehouse table that we use for all kinds of reporting. It's updated by nightly truncate and insert.
It features only data pertaining to last year onward. To deal with the reporting need for data going further back it has a design-identical clone for some previous years.
There's only one really good reason for not having all this data in the one table: transaction logging.
If we put it all in the same table we'd have to DELETE FROM Table WHERE Year >= @LastYr
and as far as I'm aware that'd pretty much double our rate of log growth.
Is there a way of...
either getting SQL server to treat these two tables as one for the purposes of programming (for table writes as well as reading so a view won't do)
...or somehow telling it to segment its data pages along the lines of the Reporting Year field such that DELETE FROM Table WHERE Year >= @LastYr can just deallocate the pages for that year. For logging purposes that seems informationally identical to what the truncate is doing already so I can't see why it shouldn't be a feature.
Getting all this in one table would make the warehouse restructure we're planning much more straightforward and avoid all the awkward UNIONs we're using in reporting.
I can imagine that if there's no way round we might just have to make do with reducing code repetition with a parametised SP but that just seems so unnecessary.
Loads of thanks!