troubleshooting Question

Delete without full logging

Avatar of MISServices
MISServices asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
4 Comments2 Solutions525 ViewsLast Modified:
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
sqlxl

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros