Temporal tables - what are the main cons?

Question

Temporal tables - what are the main cons?

VoldemarG

Hall of Fame

Points: 3883
More actions
May 31, 2022 at 8:43 pm

#4042287

While there are obvious Pros in favor of using system version tables, the new feature in 2016.
What would be most obvious several Cons for using this feature that DBAs won't want to deal with it/support/etc ?

Likes to play Chess

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

There's not much wrong with it at all. It works the same way that I wrote such auditing in the past with it being "point-in-time" enabled. It's nasty fast and I didn't have to write triggers for System Versioned Temporal tables not have to fart around with an Instead Of trigger to join the base table to the audit table for updates, etc.

One drawback that I is that they don't allow an extra column for such things as capturing the ORIGINAL_LOGIN() no matter what, like I do when I write my own. The only other substantial drawback is that whatever they use for a triggering mechanism doesn't reject updates that don't change anything and a lot of front end code does that. The ONLY reason it's a drawback is the extra rows.

As for "dealing with it" and "supporting it", it's really a no brainer. Learn it and love it because the alternative is dealing with some in-house created bit o' PITA junk that audits ever insert (instantly doubling the size of the stored data). Learning how it works will also equipment you better for writing one of your own if you've never done such a thing before or help you understand a better way even if you have OR confirm that you've been doing it anywhere to "Right" to "Better Than" the whole time.

Learn it... deeply.

--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: 1004406 More actions · Answer 2

Ah... there IS one major drawback. If you have very wide tables with only a row or two per page and a lot of different columns get updated but only a couple at a time, then you're going to need some extra space or your going to have to write some really nice "column level audits". Most people make some serious mistakes there, as well. More than double the space for everything that has changed because people make the crazy mistake of capturing both the old value and new value on a single row for every bloody change and then don't make the table with SCDs (slowly changing dimensions) to make it PiT (point-in-time).

Of course, such wide tables are usually the end result of really poor design, to begin with. There are some rare exceptions but poor design is the usual case.

--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)

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

Another con is managing how long you keep history in the versioned table. There are several options available - and each one has significant requirements that need to be designed into any final solution.

One of the options is not available until 2017 - and a later CU, which is the easiest option to implement.

Another potential issue/concern is the fact that your system time will be done using UTC time. Not really a con - but if you are not aware of that it could cause you issues when looking at historical data.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs