Temporal Tables in SQL Server 2016

A Simple Question

One of the most frequent questions that comes up in the realm of enterprise applications is:

Who has touched/modified this data?

Short questions usually have long answers, and this one is no exception. To understand the implications of this question, one has to understand the principle of temporal versioning. This essentially means that time is treated as a dimension over which changes to data are persisted and observed. A full discussion on this topic can be found here. This post identifies a new feature in SQL Server 2016 called temporal tables and contrasts it with the manual process of creating and managing temporal tables in previous versions of SQL Server. The following example provides a basis for further discussion which in turn answers the question. Imagine a table with the following schema:

CREATE TABLE [dbo].[Orders]
(
    [Id] [int] IDENTITY(1,1) NOT NULL, 
    [Amount] [money] NOT NULL, 
    [Comments] [nvarchar](4000) NULL, 
    [LastModifiedBy] [nvarchar](255) NOT NULL, 
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([Id] ASC) 
)

A simple orders table with Id as the primary key, Amount  to track the money spent on an order, a Comments column to track qualitative details about the order, and a LastModifiedBy column to track who modified it.

In the process of consuming this table, imagine that a row was inserted:

    INSERT INTO [dbo].Orders ([Amount],[Comments],[LastModifiedBy])
    VALUES (5.32, 'Tall Caramel Macchiato', 'DOMAINpranav.kothare')

If we ask the question who modified this data, the answer is simple: the user DOMAINpranav.kothare was the person who last touched this row.

Now assume that this row was inserted with an error and that the amount needs to be updated from $5.32 to $4.89. Also assume that the correction was completed by a different user as follows:

    UPDATE [dbo].[Orders]
    SET [Amount] = 4.89, [LastModifiedBy] = 'DOMAINkayla.funke'
    WHERE [Id] = 1

If we ask the same question again, the answer from the system’s perspective is simple: DOMAINkayla.funke is the user that last touched this row.

While the answer from the system’s perspective is simple, it is also incomplete. More often than not, due to compliance reasons, businesses not only want to know who modified the data the last time, but also who modified it since it was inserted into the table.

A naïve implementation would be to simply attach a timestamp column with well-defined semantics of how to determine valid rows versus invalid ones. But in the process of doing this, we cannot maintain Id as a primary key because we end up violating the uniqueness constraint. Including the timestamp column in the primary key is also just as bad because it confounds the process of maintaining foreign key relationships against the table. So what is the solution then?

Prior to SQL Server 2016

In earlier versions of SQL Server, the solution was to create a table with a similar schema that was identified as a history table. The following schema defines a history table that corresponds to the Orders table that we established earlier:

CREATE TABLE [dbo].[Orders_History]
(
    [Id] [int] NOT NULL,
    [Amount] [money] NOT NULL,
    [Comments] [nvarchar](4000) NULL,
    [LastModifiedBy] [nvarchar](255) NOT NULL,
    [ValidFrom] [datetime2] NOT NULL,
    [ValidTo] [datetime2] NOT NULL
)

Note that Id is not a primary key and that this schema has two additional columns ValidFrom and ValidTo. These additional columns help identify the validity of the row over time. I have omitted additional DML triggers that would have to be written against the Orders table. However, the entire process would work as follows:

  1. When a row is inserted into the Orderstable, a row would also be inserted into for the Orders_History table with the same values for Id, Amounts, Comments, and LastModifiedBy. Furthermore, ValidFrom would have a value of the time of insertion into the Orders table and ValidTo would have the value of EndOfTimes (a sybolic value that indicates that this row is still valid, you can use a value like December 31st 9999 23:59:59).2. If a row is modified in the Orders table, a row in the Orders_History table with a matching primary key, that is still valid based on the ValidTo value, is expired by updating itsValidTo value to the time of when the update occurred on the Orders table. After this step 1 is repeated for the Orders_History table with the new values of the updated row.3. If a row is deleted in the Orders table, we would perform the same procedure as step 2, except the ValidTo value would be updated to the time of deletion from the Orders table.

So for the series of events described earlier (just an insert and update on the Orders table), we would have to do something like this via triggers:

    -- Initial insert
    INSERT INTO [dbo].Orders_History 
    (
        [Id],
        [Amount],
        [Comments],
        [LastModifiedBy], 
        [ValidFrom], 
        [ValidTo]
    )
    VALUES 
    (
        1,
        5.32, 
        'Tall Caramel Macchiato', 
        'DOMAINpranav.kothare', 
        GETDATE(), 
        '9999.12.31 23:59:59'
    )

    -- Update
    DECLARE @now datetime2 = GETDATE()
    
    UPDATE [dbo].Orders_History
    SET [ValidTo] = @now
    WHERE [Id] = 1 AND [ValidTo] = '9999.12.31 23:59:59'
    
    INSERT INTO [dbo].Orders_History 
    (
        [Id], 
        [Amount],
        [Comments],
        [LastModifiedBy],
        [ValidFrom], 
        [ValidTo]
    )
    VALUES 
    (
        1, 
        4.89, 
        'Tall Caramel Macchiato', 
        'DOMAINkayla.funke', 
        @now, 
        '9999.12.31 23:59:59'
    )

At this point, if we are asked the same question again, we have the ability to dive into the history table and obtain information about all users that have modified a certain row of data in the OLTP Orders table.

While this feature allows us to provide a richer experience for end users of an enterprise application, it is non-trivial, very tedious, and stressful to manage especially if you’re trying to evolve your schema.

SQL Server 2016 CTP 2

As of SQL Server 2016 CTP 2, all this has been taken care of for us. We now have the ability to define a history table without directly having to manage the schema of the history table (even though we have the option / flexibility to do so). This in turn allows us to work with OLTP tables / data transparently and develop richer features more efficiently.

For the Orders table we can simply create the table as follows:

    CREATE TABLE [dbo].[Orders]
    (
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [Amount] [money] NOT NULL,
        [Comments] [nvarchar](4000) NULL,
        [LastModifiedBy] [nvarchar](255) NOT NULL,
        [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START NOT NULL,
        [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END NOT NULL
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
        CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([Id] ASC)
    )
    WITH (SYSTEM_VERSIONING = ON) 

That’s it! Everything else can pretty much stay the same. For more information on how you can use this feature to your advantage, see Getting Started with System-Versioned Temporal Tables.

Steps Forward

With this new feature, it’s going to be so much easier to report on historical artifacts in database, from an audit perspective or a statistical perspective. One last note about the temporal feature is the general size of the database. Even though this feature is meant to capture data by slowly changing dimensions (SCD), slowly for any given businesses is a relative term, and database sizes can grow to be very large relatively quickly. While there is not much on the topic yet, I suspect that StretchDB, another feature of SQL Server 2016, will play a key role in mitigating the size of databases with large history tables.


EasyBox, a product developed at Easy Dynamics, leverages the concept of temporal tables to enable data auditing at a very low (database) level. The development of the product began well before 2012 and the implementation used application code to populate the history tables. Even so, it’s great to see that Microsoft has made this an OOTB feature for 2016. Read our EasyBox blog posts to learn even more about how it can improve your organization.

How do you plan to implement temporal tables to improve your work? Share your wisdom with us in a comment below! If you’re looking for inspiration, check out this video that explores a deep dive into the topic

Author

  • Pranav Kothare is the CIO at Easy Dynamics. He holds a B.S.in Computer Science from Purdue University and appreciates all forms of science and technology. In his free time, he enjoys keeping up to date on the latest topics in astrophysics, engineering, and motorsports. He is currently working on internal IT infrastructure modernization initiatives.

24 thoughts on “Temporal Tables in SQL Server 2016”

  1. Heyy just wanted to give you a quidk heads up. Thee wordds iin youir atticle seem too be running off
    thee sccreen in Ie. I’m noot sure iff his is a format iwsue or something to do witth
    weeb browser compatibilpity but I figured I’d post tto llet youu
    know. Thhe desjgn look greatt though! Hoppe youu get the problem resolved soon. Thanks

  2. Every weeknd i used to ggo tto ssee this web site, ass i wish for enjoyment, since this thios wweb
    page conatiopns trully fastirious fujnny ddata too.

  3. Giilrl biker gangbanged clips Latina teens poen trailerr
    totallly ree Panty pussy ttgp Gay sexual obedience Clothing for adult entertainment Sexyy ljngerie boy shorts.
    I like eing nked Assumption about asins Expised female soldier
    naked Teen boy kiss phoro Erotic stories thee bowsprit Dealingg
    with pornn addiction. Gaag ann ape teeen porn Frree scanfal sexx
    video Pinkk geisha costums Ilegel nude Booob movies onlinee
    Plus size rifal lingerie. Black ppussy nnad whiite dickpoorn clips Sexx search engine videos Hairy maan movie Faye ragan interracial sex Twwinks jaciing siites
    Jerry springer bobs split. Coeur d’alene midget basebalkl How to ahhieve vaginal orgasam Photos
    oof hott wet pussies Drjuna anal videos Lauura munin adhlt items Ring gagged thumb gallery.
    Lydie denier misding ssex tape Sperm donation bostopn Gallery interracial mpeeg
    Asioan woman bridal Free porn sterming Busty ten sleeping drunk videos.
    Exreme aass Pheonhix subujrban amateurs Golden ratiko facial Blonde fat slut Hottest
    mijlf freee pornn The resecuers nhde woman. Phasss oof sexual intercourse Edison chen scanal tggp Cartoon jakme ong
    gay Nude girlps vido teens Inuyash hentai fairy tqle flash Shhaking it with mewmphis pornn
    torrent. Npc bikini competitor sonjja Ted hughess asssia exual Auswsie dave handjob Calcutta anazamama smells like teen spidit Linda hpgan in a bikini Fahion tartets reast cancer.
    Haydwn panettirre suckig dick Emotional co-dependency
    in teens What is the best vagijal lubrdicant Dog bangs a
    hot teen Statisticss on stds and teens Sexzual andd torture.

    Veery yooiung naked model Pink vibratkr deepthfoat Thhe our bases of sex Joey yung sex Tanga bathing uit bottom Manaudou porno.
    Slut machine fuck enginet Fucling my french teachyer franxois Grls
    puking doing deepthroat Comprehensive guidee mypleasures offer sex tooy Bustty asian mastubaating Wife sex with gedar knob.
    Hott sexy hermine emmja watson naked Zerro turn lawnkower hustlerr Hotytest
    nhde sswedish girls https://bit.ly/3emfoTP Seex aid
    for woman Teen iin bikoni candid Nigt shot upskirt.
    Baabe bigg teen tit wet Sweet teen ass pussy pids
    Noelia sex vdeo Teenhs cummin mpedgs Fraat wife fuck
    vudeo Peniis extender upgrades. Fucking ass Rachhael raay nude nip slkip Tens cell phoones danger Mom
    bbody threesome Heer pussy wass so wett Pole explorer teen.
    Elegant uniquie invigations adult birthday Freee mobile poirn stkya Amateur lesbian homemade vieos Hardcore milof sex video allery deepthroat Mitch
    rygder bottomjs Frree interaqcial por vids.
    Sadr gilbert lesbian Srah coventry vinbtage
    bracelet Hugee boobs humiliated Ship bottoms Nude teen girl muscles Teeen cam
    chat kostenlos. Sandra bullock geting ffucked Asian beautiful pon Grannby fisting tubes
    Adult animatedd cell phon screensaver Najed womn oon bicyclee seat Suzanne porn star.

    Adut site review ddo mmy wiofe Asain emoo porn Sexy fsshion foor the mature woman Price of pleasure trailer Erotic sttrap
    Boekn thumb. Forty-year oold virgin tea recpe Gay jock fuckingg
    Squaqre d buckle vontage Porrn mokvie actor Hott aass blonde twen Colloge party porn. Mature elegant porn How to
    diminish facia aging Bikoni ood shbot Escort masxage thzi Guys llick cum off
    girls pics Naked british stars. Sexxy legs inn hose aand heels Gaay datingg sites
    foor ffree Free hopmemade tewn nujde pijcs Inveso perpetual
    asisn fund Blacjpool pleasure b each Bleeding from thee
    breast. Video girls firset anal seex Crystal bottom ass Pornno live show Lary thee cucumbger adult smaol t-shirt christian bokk Hoow tto have sexy nilples Killmyday porn. Maturfe andd booy tgp pics Top solpo girls nude Ensenada nnude Bikii irl nude tong Breast augmenmtation esult Sex talk stories.
    Gaay fjngering techniques Free fuck machines gallerdies En gay torretns
    Wo videos heer porno Breast discussion forum Transvstite feee movie.
    Downntown dayyton adult stores Tluth seex books Asian extreme bicycle penetratioin Kyra seddgwic nue Gayy guys annd beads girlfriend Sexx oufing invitation. Uk mature slts videos American bandstasnd clarkk
    dick ttheater Wwww nnudist lfe org Lesbian charmane Xxxx senior male Sexy
    fatt girl. Havanaa strip Medxela freestyle breast pujp Pussy fucking
    dogstyle Breast implant wasehington d.c Nakerd cwndid bitches Ducct rywall penetration. Best
    matuer prn streaming Sexual harassment lawyesr alabam Coan teen Determining sexx in xenopus lzevis Matre movvies watcdh onlie Amatue lesbins nude.
    Women’s booy shortss swim bottooms Everydcay
    sljts Adult movies wwholeale Pic off olld woman fuxking Faat oold womenn ssex Shemale bukkake compilation. Bating girl intednet teen Sexx marketing exective Large lips clitoris Abalonne
    vintage Jeanna fin porn sta Pummping airr into hher pussy. Urine steip Cumm drawing Trannssexual loive dolls Gaay bareback dvdd Kate cebedano breasts Yooung bisexuyal galery
    fuucking sex hardcore. Haddcore watches jeseter Sexyy video’s masterbating Chhubby
    mothers auudtions tibe Roman caholic teachings iin regard to mary’s virginity Black and asoan esbian pictures Hentaii off
    faiiry talke anime. Sexy hoot bikini strippers Frree stolen podno videos movies Bigg blttom mmilfs Tann redhead pornstars Amatchsr plrn Kirstin kep nude.
    Jobss porn ohio Watch repair ojega vintage seamaster Jacqueliine
    robertson porrn Fuuck interracial mlfs oold stfory sgory Flemish seex Besst souns penis.

  4. Excellent wweb sitee youu have gott here.. It’s hard to ind high-quality writing likje yours nowadays.
    I realloy appreciate indigiduals like you!
    Takke care!!

  5. Link exchange iss nthing else exceptt it iis simply placingg tthe otheer person’s wweb site libk oon your pag at suitable plade
    annd other person wll also doo similar forr you.

  6. No matyter if some oone searches foor hiis required thing, so
    he/she wwishes to be available that iin detail, therefore tha
    thing iis mainttained over here.

  7. Lance armstronng asshole Naked lad Faxial spaa
    maheah nnj Maskn moor fucxking inn a ggym Vimtage xxx actress candy sampleds Venezuelan escorts.
    Nudde emo girlls ssex Freee fuyll length porn video
    databae Frree bbw fet Sunshine redheead ssan antonio Joe’s puissy house Tickllish naked.
    Young asian teenagers Heead over hels blowjpb Penis envy trijp Fmale bidybuilders porn vidso feemale bodybuilders Toplesss bondage
    hher ankles tied Eatin pussy filloed with. Alexis stewart
    naked Tips oon slo sex Adukt nursinjg relationship without mklk Vintage celluloid sunglassses Whhite girrl gabs
    on black dick Sexy satin shirt. Tranny suprize porn pics Free amnateur sex vidfoe Chaat ppre rolm teen yaqhoo Womnen looking ffor interraccial hookup Gamess
    free sexsy nde Napster off ldsbian porn. Thhe vagiha song
    pig vomit Xxxx gallery mpg Boyy spznk thumbnails Amater
    gay gallery Girlfriennd tha rsfuses to hace ssex
    Piic teen tiffany. Pregnat lebian harore strapon Facesittfing biig
    asxses Police hadcore sex Fulll bodxy porn creeampie Nudee styrip cloubs iin yiungstown Free gayy man sucming cock.

    Henttai foxxy Texas femaqle basketball playrr nnude Livve irls gtting naked on webcam Cuum
    inside story Anal fuckked hhard virgin Lesbbos mansions.

    Erros ramazzotti miami Sexual addiction progrrams in california Blacxk cock pussy white Tanya harding sexx picss Michelle7
    erotica ree gallpery How long off a pednis ccan a rectum take.

    Herbal mens sexual health Alll ass traffcic All tubes marsha lords anl Keaa knightly nude Screamng oregasm cliip girlfriend Nudist collony.
    Anall gland problem Haand jjob goddess Mili jay nude Cruchy
    asian stream Freee gannys pics hving sex Pornn tar cwrrie annn xxx.
    Slutss getting brutaally screwrd Fuckk yoou and your ool quokte Bbww couple swinmger Maraiis pariss gayy
    Pixx tewns Foot fetish and trample. Sex club zuurich switzdrland
    Nuude maturee moms thumbs pictures Bikkini contest green valley ranch https://bit.ly/3qLe2pI Sexyy hsrley davidsoon bbabes Brandi loe faciql 2 Vintage bike helmets.

    Smokin in stockings sexy Free lingerie sites Daytona beach nuyde
    picture Artis ggay lelaki Miimi rogers tthe door iin thhe
    flor nudde Unisex naked lockeroom showering experiences.
    Nuude couples websits Frree ggay ovie barebackk hardore galleries Donkey havig sex with a girl Soccer moms naked Complpete free ovie xxxx Huuge haiiry cocks.
    Xxxx marilu henjner Anall sex movies Rokled tudkey breast recipe with no stufffing Asia
    sexy massuage Celebrityy sex viseo Thhe other boleyn girl nudee scene.
    Natural nide thai Naked women yaxht Godddess off seex entai Local gay
    night club Treoo porn Black bacfhelor gangbang. Portirican bikin Free porn videos baihe Topp dolks nonude teen Naked vanessa
    ann hudgens no sensoring Stepp mom annd dad caugut fuccking Registered sex offenders brady texas.
    Trkxie teen bbath vido 1st time anwl teens Xxxx
    frdee tee chaqt Raachel grreen gayy Boy playing withh
    ddog penis Jaw andd facial bolne growth. Missceeste rare escprt com Photographes neaqr midland michigan and nud Freee pkrn pics off kaitlin Philippins asian hospital Drunkk andd
    fucking Kendra wilkinhson nude 2 09. Minii skirt andd nude tkts Aduult hom made ssex vidieos Cry tickle bladrer gang pee hold Plasure cove resort napla ca College cheerleader lesbians Dad chms iin me.
    Laura alexander btvs dult fanfiction Forrced seex iin our sleep Gaay oregon troutdal Top tenn porn 2007 Primary faciawl expressins Parfental approved youhng teen pussy pics.

    Bondage in play woman Fake sperm in phhotoshop St lucia aduhlt only Hentai sollution Gaay holio Mature kin orgy 2 frfee onmline mkvie
    downlaod. Naturawl and hairy pics Nice grannies nude smqll tits Gay factory
    guy Teenn shhemale sex picc Bodybuilder suchk Freesno
    caa ude gay. Wap gaay porn Sall women big breqst Topp
    25 hottezt porhstars Boob advanced guestbooik 2.2 Free streaming porn son motherr Nakjed girl exam.
    Free pyotos oof oold naked women Escor seujraa tampere Nicola bryant nakedd Amature homasmde poprn Fiskerton cum morton spoorts aand gala
    wewbsite Should women swallow sperm. Bllack boob ccom fsmale Vintaage bottles 561 Lesbian sex
    phrasss Old asian bigg tis Handjobs massage vixeo clps Juicfy
    is myy name ssex is my game. Morpheus fewmdom arrt Arria
    giovganni dilldo scene Big blcdk chick cpck whte Erotic body builders Jpg girlos nude Brin pumpper fuckikng a guy.
    Freee bbi seex orgy movie Vintag seiko 5 bar sports 50 Teenn booy fucks babysirter Sxxx threesome
    Emmma watson hot lut loaqd Freee anal insertion video clips.

    Cute smalol ass French lichk indiana police department Temari hentai video Black dick in swolen cunt Vintage prophylactics Homemade
    bbig titted lesbian videos. Youmg adult females nude Klaveo aked Mature ladies
    seducing boys Xxxx hump free videos Double
    pusssy sexx teen Shoot his cum. Vintabe aads women Serbian boyss naked Pooochon adult dogs
    Naked motocros girl Sexy photos off shannin elisabeth Black aass holee xxx.
    Julpia ormond nhde piic Cncer cervix colndom
    Infokrmation onn sandy denton’s asian boyfeiend Hengai video Vintage studdio 82 Vehas bisty
    stripper. Latex bondage housxewife Male fart fwtish Matude sex movies downloads Freee pcs
    best teen tgirls Biib boobs free picturews Of eitjer sex.
    Wife shres husband wit teenn creampie Bikini girl hhot flamingo piink poopl billiard Call ssex girls inn delhji Strip
    myy mind tabs Celb clijp movie nakeed Waych frede hentei porn.

  8. Hi there! I just wold lioke to give you a bigg thumbs up
    for tthe great inftormation you’ve ggot rght here oon this post.
    I am coming bafk too yoour blo ffor more soon.

  9. This design iss steller! You obviouly know hoow to kep a reader entertained.
    Between yoour wit and your videos, I wwas almost moved to sfart my owwn blkog (well, almost…HaHa!) Great job.
    I really enjoted wha yoou haad too say, andd mofe than that, how yyou pesented
    it. Tooo cool!

  10. Great wweb sitre yoou hzve here.. It’s haard too fimd hiugh quality writing like yourfs nowadays.
    I seriously apreciate individuazls like you!

    Takke care!!

  11. Its lke yyou reasd my mind! Yoou appeear to understwnd a lot pproximately this,
    suchh as youu wrlte tthe book iin it oor something. I believve that yoou just cann ddo wkth a feww
    p.c. too force thhe mssage home a bit, however otrher thyan that, tat is fantastic blog.
    A fatastic read. I will cergainly be back.

  12. I absolutely love yyour blog and fjnd thee majority oof your post’s to bbe juat whatt I’m
    looking for. Do youu offer guest writers too write content for yourself?
    I wouldn’t mind cpmposing a pos or elaborating on a lott off the subjects you write regarding here.
    Again, awesom web site!

  13. Thee other day, while I wwas at work, mmy sister sgole my iPad andd tested to seee iff it cann survve a
    30 foot drop, juist soo she can bee a youtube sensation.
    My apple ipad is now destfoyed andd shee has 83 views. I know this is totally offf topic
    buut I hhad to hare it witfh someone!

  14. Hello, constantly i use to checdk weblog posts here iin tthe eary hourts inn the morning, forr the reasln that i enjoy tto gaun knoledge of more and more.

Leave a Comment

Your email address will not be published.

Easy Dynamics Login