Link to home
Start Free TrialLog in
Avatar of MISServices
MISServices

asked on

Delete without full logging

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
ASKER CERTIFIED SOLUTION
Avatar of sqlxl
sqlxl
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MISServices
MISServices

ASKER

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
Hope it works out.