Benefits of using SQL Server Temporal Tables - Part 1

By: Ameena Lalani   |   Updated: 2018-05-08   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | > Temporal Tables

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:

1. Speedy coding
2. Built in optimization
3. Easy maintenance
4. Granular security
5. Easy auditing
6. Transparent implementation
7. Quick data recovery

Using SQL Server Temporal Tables for Quick Data Recovery from Accidental Updates or Deletes

We will create a test database and a temporal table. Then we will insert a few rows into the table and then we will do some DML operations and finally query both tables to see the data.

USE master
GO

DROP DATABASE IF EXISTS TestTemporal;

CREATE DATABASE TestTemporal;
GO
 
USE TestTemporal
GO
 
CREATE TABLE Customer (
   CustomerId INT IDENTITY(1,1)  PRIMARY KEY
  ,FirstName VARCHAR(30) NOT NULL
  ,LastName VARCHAR(30) NOT NULL
  ,Amount_purchased DECIMAL NOT NULL
  ,StartDate datetime2 generated always as row START NOT NULL
  ,EndDate datetime2 generated always as row END NOT NULL
  ,PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory)) ;
GO

INSERT INTO dbo.Customer (FirstName, LastName, Amount_Purchased)
VALUES('Frank', 'Sinatra', 20000.00),('Shawn', 'McGuire', 30000.00),('Amy', 'Carlson', 40000.00);
GO
 
-- Now make some changes in the table
WAITFOR DELAY '00:00:30';

-- insert a row
INSERT INTO Customer (FirstName, LastName, Amount_purchased)
VALUES ('Peter', 'Pan', 50000);
GO

WAITFOR DELAY '00:00:30';

-- delete a row
DELETE FROM dbo.Customer WHERE CustomerId = 2;
GO

WAITFOR DELAY '00:00:30';

-- update a row
UPDATE Customer SET Lastname = 'Clarkson' WHERE CustomerId = 3;
 
-- Let us query both temporal and history tables
SELECT * FROM dbo.Customer;
SELECT * FROM dbo.CustomerHistory;

Here is the what the data in the tables looks like.

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.

Next Steps

• Please read more about temporal table query syntax here.
• Review this tip for another example of retrieving data from temporal tables.

About the author

Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips