How To Roll-Back Data in a Temporal Table

Published on: 2017-05-30

The Big Red Button” by włodi used under CC BY-SA 2.0 / Cropped and text added from original

You can watch this blog post on YouTube too!

So you’ve started using temporal tables because they make your point-in-time analysis queries super easy.

Your manager is happy because you’re getting historical data to him quickly. Your DBA is happy because she doesn’t have to clean up any performance killing triggers that replicate a temporal table’s functionality. Everything with temporal tables has made your life better.

Except that time when you accidentally inserted some bad data into your temporal table.


The good news is that all of your data is still intact — it’s been copied over to the historical table. Phew!

Now all you need to do is rollback this inadvertent row insertion and make your tables look just like you did before you started breaking them.

This should be easy right?

Well not exactly — there’s no automatic way to roll back the data in a temporal table. However, that doesn’t mean we can’t write some clever queries to accomplish the same thing.

Let’s make some data

Don’t mind the details of this next query too much. It uses some non-standard techniques to fake the data into a temporal/historical table with “realistic” timestamps:

If you look at the results of our temporal table (top) and historical table (bottom), they should look something like this:

This data represents my totally real, very very not-fake rental car business.

You see those two rows in the top temporal table? Those are the ones I just added accidentally. I actually had a bug in my code *ahem* and all of the data inserted after 2017–05–18 is erroneous.

The bug has been fixed, but we want to clean up the incorrect entries and roll back the data in our temporal tables to how it looked on 2017–05–18. Basically, we want the following two rows to appear in our “current” temporal table and the historical table to be cleaned up of any rows inserted after 2017–05–18:

Fortunately, we can query our temporal table using FOR SYSTEM_TIME AS OF to get the two rows highlighted above pretty easily. Let’s do that and insert into a temp table called ##Rollback:

You’ll notice we also updated the SysEndTime — that’s because a temporal table always has its AS ROW END column set to the max datetime value.

Looking at ##Rollback, we have the data we want to insert into our temporal table:

This is the data we want!

Now, it’d be nice if we could just insert the data from #Rollback straight into our temporal table, but that would get tracked by the temporal table!

So instead, we need to turn off system versioning, allow identity inserts, delete our existing data, and insert from ##Rollback. Basically:

While system versioning is off, we can also clean up the historical table by deleting all records after 2017–05–18 by joining the ##Rollback temp table on SysStartTime:

We have rolled back our data successfully!

Only One Tiny Problem

Did you notice that the last SysEndTime values in our historical table don’t match up with the SysStartTime values in our temporal table?

This is a data integrity issue for our temporal table — our datetimes should always be continuous.

Fortunately this is easily fixed with one more UPDATE statement:

Our correctly rolled back temporal table

Finally, remember to turn system versioning back on and to turn off our identity inserts to restore the original functionality of our temporal tables:

Congratulations, you’ve rolled back your temporal table data!


Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

2 thoughts on “How To Roll-Back Data in a Temporal Table”

  1. I have a few questions
    can we track inserts with temporal table,
    can we join history table with dimention tables,
    can we use the history table in reports ?

    1. Hi Hiedi, temporal and history tables behave like regular tables in SQL Server – you can use them jointly or independently in reporting, querying, etc… Don’t take my word for it though – best thing to do is to test out the functionality in your own specific use case 😊

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.