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.

Whoops

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 OBJECT_ID('dbo.CarInventory', 'U') IS NOT NULL 
BEGIN
 -- When deleting a temporal table, we need to first turn versioning off
 ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = OFF  ) 
 DROP TABLE dbo.CarInventory
 DROP TABLE dbo.CarInventoryHistory
END;
CREATE TABLE CarInventory   
(    
 CarId INT IDENTITY PRIMARY KEY NOT NULL,
 Year INT,
 Make VARCHAR(40),
 Model VARCHAR(40),
 Color varchar(10),
 Mileage INT,
 InLot BIT NOT NULL DEFAULT 1   
);
CREATE TABLE CarInventoryHistory  
(    
 CarId INT NOT NULL,
 Year INT,
 Make VARCHAR(40),
 Model VARCHAR(40),
 Color varchar(10),
 Mileage INT,
 InLot BIT NOT NULL,
 SysStartTime datetime2 NOT NULL, 
 SysEndTime datetime2   NOT NULL 
); 

INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',0,1,'2017-05-13 8:00:00.0000000','2017-05-14 8:00:00.0000000');
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(2,2017,'Chevy','Malibu','Silver',0,1,'2017-05-13 8:00:00.0000000','2017-05-14 9:00:00.0000000');
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',0,0,'2017-05-14 8:00:00.0000000','2017-05-15 7:00:00.0000000');
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(2,2017,'Chevy','Malibu','Silver',0,0,'2017-05-14 9:00:00.0000000','2017-05-19 15:00:00.0000000');
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',73,1,'2017-05-15 7:00:00.0000000','2017-05-16 10:00:00.0000000');
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(2,2017,'Chevy','Malibu','Silver',488,1,'2017-05-19 15:00:00.0000000','2017-05-20 08:00:00.0000000');
ALTER TABLE dbo.CarInventory
ADD SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
 CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME(),
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
 CONSTRAINT DF_SysEnd DEFAULT '9999-12-31 23:59:59.9999999',
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
SET IDENTITY_INSERT dbo.CarInventory ON;
INSERT INTO dbo.CarInventory (CarId,Year,Make,Model,Color,Mileage,InLot) VALUES(1,2017,'Chevy','Malibu','Black',120,1);
INSERT INTO dbo.CarInventory (CarId,Year,Make,Model,Color,Mileage,InLot) VALUES(2,2017,'Chevy','Malibu','Silver',591,1);
SET IDENTITY_INSERT dbo.CarInventory OFF;
-- We need to make sure that the last SysEndTimes in our historical table match the SysStartTimes in our temporal table
DECLARE @LastSysStartTimeInTemporalCar1 DATETIME2, @LastSysStartTimeInTemporalCar2 DATETIME2
SELECT @LastSysStartTimeInTemporalCar1 = SysStartTime FROM dbo.CarInventory WHERE CarId = 1
SELECT @LastSysStartTimeInTemporalCar2 = SysStartTime FROM dbo.CarInventory WHERE CarId = 2
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(1,2017,'Chevy','Malibu','Black',73,0,'2017-05-16 10:00:00.0000000',@LastSysStartTimeInTemporalCar1);
INSERT INTO dbo.CarInventoryHistory (CarId,Year,Make,Model,Color,Mileage,InLot,SysStartTime,SysEndTime) VALUES(2,2017,'Chevy','Malibu','Silver',488,0,'2017-05-20 08:00:00.0000000',@LastSysStartTimeInTemporalCar2);
ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CarInventoryHistory));
-- If everything worked well, we should see our data correctly in these table
SELECT * FROM dbo.CarInventory
SELECT * FROM dbo.CarInventoryHistory

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:

DROP TABLE IF EXISTS ##Rollback
SELECT
 *
INTO ##Rollback
FROM 
 dbo.CarInventory
FOR SYSTEM_TIME AS OF '2017-05-18'
-- Update the SysEndTime to the max value because that's what it's always set to in the temporal table
UPDATE ##Rollback SET SysEndTime = '9999-12-31 23:59:59.9999999'

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:

ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = OFF);
SET IDENTITY_INSERT dbo.CarInventory ON;
DELETE FROM dbo.CarInventory WHERE CarId IN (SELECT DISTINCT CarId FROM ##Rollback)
INSERT INTO dbo.CarInventory (CarId,Year,Make,Model,Mileage,Color,InLot)
SELECT CarId,Year,Make,Model,Mileage,Color,InLot
FROM ##Rollback

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:

DELETE h
FROM ##Rollback t
  INNER JOIN dbo.CarInventoryHistory h 
  ON
    h.CarId = t.CarId
    AND t.SysStartTime <= h.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:

UPDATE t
SET t.SysEndTime = i.SysStartTime
FROM dbo.CarInventoryHistory t
 INNER JOIN ##Rollback r 
  ON t.CarId = r.CarId
  AND t.SysEndTime = r.SysStartTime
 INNER JOIN dbo.CarInventory i
  ON t.CarId = i.CarId
  AND r.CarId = i.CarId
SELECT * FROM dbo.CarInventory
SELECT * FROM dbo.CarInventoryHistory
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:

ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CarInventoryHistory));
SET IDENTITY_INSERT dbo.CarInventory OFF;

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!

3 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.