Delete without full logging

MISServices
MISServices used Ask the Experts™
on
Hi,

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!

Adam
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Set it up as a single partitioned table on ReportingYear.

Then you can program as one table for read/write. You can also use partition elimination in your queries and when data is too old to be useful, just drop that partition.
AneeshDatabase Consultant
Top Expert 2009
Commented:
Did you by nay chance tried the table partitioning; partitioning by year seams to be a better option here, you can drop the partition and recereate it before populating the data

Author

Commented:
Cheers,

The word partition was in the back of my head when writing my question because I'd heard without knowing the meaning of but it was rattling round back there sounding relevant.

The only reason I'm awarding B not A is because I obviously I now need to read up on partitions though obviously I wasn't expecting a tutorial.

Thanks again

Commented:
Hope it works out.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial