SQL Server Temporal Tables vs Change Data Capture vs Change Tracking - part 3

By:   |   Comments (10)   |   Related: > Change Data Capture


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.


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


USE TestTemporal

,FirstName VARCHAR(30)
,LastName VARCHAR(30)
,Amount_purchased DECIMAL
,StartDate datetime2 generated always as row start
,EndDate datetime2 generated always as row end

INSERT INTO dbo.Customer (  FirstName,    LastName,    Amount_Purchased)
VALUES( 'Frank', 'Sinatra',20000.00),( 'Shawn', 'McGuire',30000.00),( 'Amy', 'Carlson',40000.00)

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)

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

-- update a row
UPDATE Customer
SET  Lastname = 'Clarkson' WHERE CustomerId = 3

-- Let us query to see what it reports
SELECT * FROM dbo.Customer
Select * from dbo.CustomerHistory

This is the data we just created.

SQL Server Temporal Table query results 1

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

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

SQL Server Temporal Table query results 2

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.

SQL Server Temporal Table query results 3

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.

Features CT CDC Temporal
Tracks Data Changes Yes Yes Yes
Tracking mechanism Synchronous Asynchronous Synchronous
Require enabling at Database Level Yes Yes No
Require enabling at Table Level Yes Yes Yes
Historical Data retention No Yes Yes
Ability to Recover data from history No Yes Yes
Requires change in current code Yes Yes No
Table requires primary Key Yes Yes Yes
Schema changes to the table allowed Yes No Yes
Ability to analyze trends in data No No Yes
Available in Standard Edition of SQL Server Yes No Yes
History data is secure No No Yes
Can you truncate history table? Yes Yes No
History data resides in the same table No No yes
Can you truncate main table? Yes No No
Dependency on SQL Server Agent running No Yes No


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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

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

View all my tips

Comments For This Article

Sunday, May 28, 2023 - 4:06:07 AM - Tim Braes Back To Top (91230)
Change Data Capture is supported in Standard Edition since SQL Server 2016 SP1. It's still unsupported in Web and Express editions.

Friday, August 13, 2021 - 6:59:58 PM - Madhuri vasireddy Back To Top (89127)
Thanks Ameena.
Great article
We have implemented CDC and I was always doubtful that is there a better option than CDC, due to redundancy of data with CDC. But, this article gave me a clear picture.
For my requirement CDC is better as we can’t create primary key on every table eg transaction tables.

Thanks again

Wednesday, July 7, 2021 - 12:24:04 PM - Chris Bordeman Back To Top (88961)
You say "Temporal and history table schema should be same." I just tried making them different, and there was no error. Why "should" they be the same?

Tuesday, April 13, 2021 - 4:19:17 PM - Green Grasso Home Back To Top (88525)
None of these adds the one ingredient that would turn it into a security-oriented auditing feature: the login/user who made the change being recorded.

Thursday, March 5, 2020 - 2:48:49 PM - Tot Back To Top (84960)

CDC allows to control how long we can keep the history.

CDC also records what columns were updated which is great feature to analyze trends.

CDC will truncate history table by disabling and enabling CDC on a table.

CDC does not require change in the current code.

Thursday, January 3, 2019 - 7:13:27 AM - Ashraf Back To Top (78613)

Hi Ameena,

Thank you for such a nice Article. I have some questions regarding the features which are compared above.

  1. History data is secure: How history data is not secured for CDC ? Can you please elaborate a little bit. As history data for CDC resides in a table which exists under cdc Schema; history data for temporal table resiedes in table which exists in same schema as Main table.
  2. History data resides in the same table: For Temporal Table history data is stored in different table which we specify while enabling SYSTEM_VERSIONING. Then why You mentioned that Hisotry data resides in same table?
  3. Ability to analyze trends in data: Why we cannot analyze the trends of data in CDC, as it also store the hostoric data in database.

Wednesday, January 2, 2019 - 7:31:38 PM - Shimmy Back To Top (78608)

Still not sure what what be better CDC or temporal tables (TT).

I have a wiki-like website that the community is able to update some of its parts, all structured data (no plain text docs as Wikipedia), spanning a complex graph of multiple tables.

I thought TT would be best because it's easiest to implement and use from .NET and EF Core, but my main goal is to treat the data as solid 'versions' rather than 'what my data looked a year ago'.

Saturday, December 29, 2018 - 10:39:07 AM - Shimmy Back To Top (78575)

Yes definitely, thank you Ameena!

I'm now looking for ways to integrate them and use them using EF Core.


Thursday, December 27, 2018 - 11:06:10 AM - Ameena Lalani Back To Top (78568)


Glad to see that you found these articles on temporal tables useful.

Wednesday, December 26, 2018 - 8:03:38 PM - Shimmy Back To Top (78563)

Thank you!

Great article, just what I was looking for!

get free sql tips
agree to terms