Benefits of using SQL Server Temporal Tables - Part 1

Problem

You probably know what SQL Server
temporal tables are by now, but do you know all of the benefits of using them?
In this tip we cover some aspects to help you make an informed decision when building
your next application about why and how to use SQL Server temporal tables.

Solution

Temporal tables are useful in applications where tracking of data changes is
required. Let’s learn about some of the key benefits of using temporal tables
in this tip.

This is part 1 of a series of tips to explain various benefits of using temporal
tables. In each tip in this series, we are going to present a different example
of using SQL Server temporal tables and from it learn about the usefulness of this feature
in detail.

Each scenario will be tagged with one or more benefits from the following list:

After running the above code, we will see all the changes reflected in Customer
and CustomerHistory tables.

Recover Deleted Record for SQL Server Temporal Table

If we want to recover the data we deleted we simply have to find the record id
and time the delete operations happened to bring the data back to the main temporal
table. Here is how it is done.

-- recover one row that we deleted
-- this table has an identity column so we need to allow inserts using this command
SET IDENTITY_INSERT dbo.Customer ON
INSERT INTO dbo.Customer(CustomerId, FirstName, LastName, Amount_purchased)
SELECT CustomerId, FirstName,LastName, Amount_purchased
FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2018-04-19 18:16:43.3351187'
WHERE CustomerId =2
-- this table has an identity column so now we need to turn off inserts using this command
SET IDENTITY_INSERT dbo.Customer OFF

The time value used here is the one where this customer record was valid (for
example at the time of insert). The FOR SYSTEM_TIME clause
AS OF made it a breeze to get the data back from the history table
and insert the data into the Customer table. We did not have to perform any joins.
If the table does not have an identity column (CustomerId) then you don’t
need to do Identity_Insert ON and OFF.

Recover Updated Data for SQL Server Temporal Table

Now let’s recover the old last name of Amy (Carlson) that was updated to “Clarkson”.
Here, FOR System_Time clause is acting as a history table joined to the Customer
table to get the updated value.

-- Let's look at the old value of CustomerID =3
SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2018-04-19 18:16:43.3351187'
WHERE CustomerId = 3
-- Let's look at the current value of CustomerID =3
SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2018-04-19 18:18:13.3820395'
WHERE CustomerId = 3

Here are the results.

Here is the command we can use to get the data back.

-- Recover old value of the updated row
UPDATE dbo.Customer
SET LastName= history.Lastname
FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2018-04-19 18:16:43.3351187' as history
WHERE history.CustomerId = 3 and Customer.CustomerId = 3
-- Let us query both temporal and history tables
SELECT * FROM dbo.Customer;
SELECT * FROM dbo.CustomerHistory;

Here are the results.

Again we see how easy it is to update the values in the current temporal table
with values from the history table. One thing to note is that this is actually a
second update to the CustomerId = 3 record and hence you will see one more row in
the history table. In the example above of recovering deleted data, it was an insert
statement to the Customer temporal table and therefore no history row was generated
for it. We can interpret that values in the history tables were valid during the
period of the StartTime and EndTime dates.

Show list of all changes made to a SQL Server Temporal Table

Now letís say we want to audit the data to show all changes for all records in
a table or for just one record. You need to use the ALL clause
of the FOR SYSTEM_TIME.

SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME ALL
ORDER BY StartDate;
-- All records for Amy
SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME ALL
WHERE CustomerId = 3
ORDER BY StartDate;
-- All records for Shawn
SELECT *
FROM dbo.Customer
FOR SYSTEM_TIME ALL
WHERE customerId = 2
ORDER BY StartDate;

Here are the results.

Summary

In this tip, we saw that SQL Server temporal tables are an excellent feature to use where
data tracking of a mission critical application is required. This is because it
is so easy to setup and retrieve data for auditing as well as for recovery of data
from accidental updates and deletes that are super-fast and simple to achieve.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

This temporal table series is very interesting and I learnt it something new.

Thanks for this article.

-Uday

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.