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
Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
sqlxl

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
sqlxl

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Aneesh

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
sqlxl

Hope it works out.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck