Tracking History in Fact Table

Question

Tracking History in Fact Table

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Niggles Old Hand Points: 352 More actions · Answer 1

Jeff Moden wrote:

My personal feeling is that CDC is incredible overkill for simple DML logging of historical data both in complexity of use and design as well as causing excessive duplication of data and a shedload of other stuff that goes with it like LSNs, etc, etc. It even creates a sequence number within transactions. All of that just isn't necessary for simple logging of historical data.
If you need to synchronize devices, then I can see the utility in using it for that but not much more. Even if you have to do column based rather than row based auditing, there are better and much simple/less resource expensive methods than CDC.
Again, that's just my opinion.

Hi Jeff,

We might be talking about two different approaches here. I’m referring to Change Tracking not Change Dara Capture. I agree CDC would be overkill. Change tracking is a lighter solution I believe.

Thanks,

N

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

I believe you're correct in that. However, according to the following link (which is what I have to go by because I've not instantiated either because it was simpler to build a handful of triggers with code that I wrote to make it easier), Change Tracking doesn't keep history (at least not "forever",if I'm understanding correctly), which I believe is what you need.

Here's the chart from that page that I'm talking about...

Don't get "history" mixed up with "Retention Period". My understanding is that there is no "forever" version of the "Retention Period".

To be sure, though, and to say it again, I've never used either because they both seemed too complicated or were missing something that I needed them to do (keeping indefinite history was one of them) and so I may have misread something along the line.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

Something else to think about... I needed to be able to use the BULK LOGGED recovery model and I'm pretty sure that both of these methods (CDC and CT) both rely on the log file. That was another reason I blew off the idea of using either one. Again, though, I could be wrong especially about CT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Niggles Old Hand Points: 352 More actions · Answer 4

Jeff Moden wrote:

Something else to think about... I needed to be able to use the BULK LOGGED recovery model and I'm pretty sure that both of these methods (CDC and CT) both rely on the log file. That was another reason I blew off the idea of using either one. Again, though, I could be wrong especially about CT.

Thanks.... Just to lend weight to your argument I read in section 2 (historical record tracking using triggers) of the link below that triggers are around 35% faster than using the merge output to achieve the same end. It seems using the exec sp_settriggerorder = last is recommended.

https://www.sqlshack.com/are-sql-server-database-triggers-evil/

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

Shifting gears a bit, you good folks are still on SQL Server 2012. My recommendation is that you need to consider upgrading. After all, 2012 is 7 years old now.

I'm a big loath to upgrade to a new version when it's still basically an RTM release so I would be really leery of jumping straight to 2019 until, say Q3 of 2020 but I'd definitely suggest making the move to 2017. If you do that, you'll enjoy multiple wins including being able to do what you want with the simplicity of using Temporal Tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)