SQL Temporal Tables vs Change Data Capture vs Change Tracking – part 3

Problem

In part 1 and part 2 of this 3-part series, we talked about Change Tracking (CT) and Change Data Capture (CDC) features of SQL Server. Although, these pre-SQL Server 2016 data tracking technologies have their own uses, SQL Server 2016 Temporal Table is the far superior option. Let us learn more about it.

Solution

Before we start comparing SQL Server 2016 Temporal Tables with Change Data Capture and Change Tracking, let us dive briefly into what a Temporal Table is and how it works.

Temporal Tables, when enabled, create a history table automatically and keeps track of updates and deletes to the main table. Either of the tables cannot be dropped as long as the system versioning is on. This protects against an accidental drop of the history table. The history table is attached to the main table itself. You don’t have to create any other objects such as another table or a trigger manually. Additionally, querying data from temporal and history tables is a breeze, as we will see from the example discussed below.

Unlike CDC and CT, no code change to your existing queries are required when you turn on the Temporal Table feature.

How does the SQL Server Temporal Table feature work?

To understand this, we are going to use the same example using the Customer table as we did in the past tips (part 1 and part 2). Temporal Table syntax is a little bit different, but nothing too hard to understand. This feature is also very well documented on Books Online.

Some requirements of a temporal table are:

• Table has to have a Primary Key.
• Table should have 2 datetime2 columns indicating one as a start of period and another column as an end of period during which time values of a particular row are valid.
• System Version should be enabled at the table level.
• Temporal and history table schema should be same.

Now let’s see how a temporal table works. We will create a database, table and insert some data.

 USE master
GO
CREATE DATABASE TestTemporal
GO
USE TestTemporal
GO
CREATE TABLE Customer (
CustomerId INT IDENTITY(1,1)  PRIMARY KEY
,FirstName VARCHAR(30)
,LastName VARCHAR(30)
,Amount_purchased DECIMAL
,StartDate datetime2 generated always as row start
,EndDate datetime2 generated always as row end
,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
SELECT * FROM dbo.Customer
-- Now make some changes in the table
-- insert a row
INSERT INTO Customer(FirstName, LastName, Amount_purchased)
VALUES('Ameena', 'Lalani', 50000)
GO
-- delete a row
DELETE FROM dbo.Customer 
WHERE CustomerId = 2
GO
-- update a row
UPDATE Customer
SET  Lastname = 'Clarkson' WHERE CustomerId = 3
GO
-- Let us query to see what it reports
SELECT * FROM dbo.Customer
Select * from dbo.CustomerHistory

This is the data we just created.

The history table only tracks updates and deletes to the main table. When new rows are inserted, they do not have any history yet so they don’t get tracked in the history table, but they are assigned a system time which marks the beginning of the validity period. As soon as changes are made to these rows, they start to get tracked and the rows are assigned another system time which marks the end of the validity period. Temporal Tables show the current state of data and the history table keeps each previous version of the row. In this example, the current last name of CustomerId=3 is stored in the temporal table and the old value is stored in the history table. If we again update the same row, the temporal table will show the latest last name and another row will be added to the history table showing another change for this row.

 -- Update the above row one more time
UPDATE Customer
SET  Lastname = 'Blacksmith' WHERE CustomerId = 3
GO
-- Let us query to see what it reports now
SELECT * FROM dbo.Customer
SELECT * FROM dbo.CustomerHistory 

We can see the LastName for customerID = 3 equals “Blacksmith” and the history table contains the prior to versions.

Querying SQL Server Temporal Tables

To see the value of a row at any given time in the history, use the clause FOR SYSTEM_TIME in the SELECT statement as shown below. Using the sub clause “ALL” will give you the complete history along with the current state of the data.

 SELECT Customerid, FirstName, LastName, StartDate, EndDate 
FROM dbo.Customer  
FOR SYSTEM_TIME AS OF '2017-11-14 05:25:30.9721217';
SELECT Customerid, FirstName, LastName, StartDate, EndDate 
FROM dbo.Customer  
FOR SYSTEM_TIME ALL order by EndDate ; 

We can see we have the record for customerID = 3 at a specific time returns that row and the second result returns all of the records both from the table and the history table.

The first query returns the state of data at the specific point in time. The second query returns all data from current and history table. You can manipulate your queries to get fancier results for your application, but the point is that out of the box a temporal table provides you with the ability to get intelligent data without the need to write complex code. This is a very valuable in finding trends and anomalies in your data over a period of time.

I said earlier, enabling a temporal table does not require any change to your existing code. Suppose your current code uses a view which has “Select * from Customer”. Your application is expecting a certain number of columns from this view. Now you have made the table temporal by adding two system time versioned columns.  You would think that this will break your code, but the HIDDEN property of the column in a temporal table is your best friend and will come to your rescue. The HIDDEN property is optional and will hide these columns from a standard SELECT statement for backward compatibility with your application and queries. Hence no code change is required when you make the existing table temporal with the HIDDEN clause.

Compare Features

Now you have a basic understanding of each data tracking technology and how they work, so you are in better position to compare and contrast some of the features and functionalities.

Summary

As SQL Server data professionals, we now have several options available for tracking data changes. Additionally, we can always create our own solutions or purchase 3rd party vendor products. But we all are smart enough to know the value of not re-inventing the wheel and use the tools provided natively in the software. Based on your own database and application environment, you can make better and creative use of these tools. What makes sense for one area of business might not work in other areas, so hopefully I have helped you decide which of these data tracking tools makes more sense for you.

Next Steps

• Read more about CDC and CT here.
• Read previous tips Part 1 and Part 2 of this series.
• Check out Temporal Table considerations and limitations here.