Temporal tables - real world opinions?

Question

Temporal tables - real world opinions?

SQLian

SSC-Addicted

Points: 413
More actions
November 18, 2019 at 10:33 am

#3698460

Although I have worked with SQL Server for many years, I only recently came across the concept of temporal tables (yes, clearly I have been hiding under a rock all this time). A bit of googling promptly provided plenty of "how to" information.
However, it is not so easy to find out thoughts and opinions from people who have got their hands dirty with it in the real world. Conceptually, it looks pretty good. But I am aware there have been "pretty good" things in the past which have ended up being either unused or quickly becoming too complicated/fiddly to use in anger.
I would be grateful for any thoughts.

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

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

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

Ok... just my 2 cents and, like you, I've not been using them for long and so I could still be missing a nasty caveat or two but, so far, it's been good to go.

Not having an automatic filling in of a "Modified_By" column in the history table is a pain. You would have thought they would have added that.

Overall, though, it's not bad as a canned bit of functionality. They did a pretty good job, IMHO. It does follow SCD Type 6 (also known as a combination of SCD Types 2 an 4) and that's damned handy.

Ah... almost forgot. I cannot believe that they force you to have the SCD history table in the same database. It would have been absolutely awesome if the allowed it to be stored in a separate database because SCD history tables frequently turn out to be the largest tables in any database. The work around is that SWITCH OUT does work and so you can create a work around but it would have been nice to have. They could have at least made it work through a synonym or pass-through view.

They do have quite a few other restrictions but I probably won't ever bump into any of the ones that are documented.

Overall, I'm still happy with it because I will no longer have to put up with developers an others building the improper flavor of the day.

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

SQLian SSC-Addicted Points: 413 More actions · Answer 2

Thank you for the reply. This was the sort of info I was hoping for.

I will likely pursue this route in the next appropriate project. I was wary about wading too deep and then having to back out to traditional tables.

Re the Modified_by field, while I understand the irritation of it not being included, I can also see why they may have missed it out given connection criteria often do not equate to end user details.

Thank you once again.