Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Free SQL Server Webcast > Building Really Fast SQL Server VMs
 

Benefits of using SQL Server Temporal Tables - Part 1


By:   |   Read Comments (2)   |   Related Tips: More > 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.

results

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.

customer id

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.

first name

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.

customer id

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.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Ameena Lalani 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.

View all my tips





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.



    



Wednesday, May 09, 2018 - 4:03:37 PM - Ameena Lalani Back To Top

 Uday,

Stay tuned for more in upcoming months. Thanks for the comment.

 


Tuesday, May 08, 2018 - 8:04:34 PM - Uday Back To Top

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

Thanks for this article.

-Uday


Learn more about SQL Server tools