Post reply

Temporal tables - real world opinions?

  • November 19, 2019 at 4:38 am

    #3698723

    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)

  • November 22, 2019 at 2:36 pm

    #3700022

    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.

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

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