Introduction to SQL Server Temporal Tables

By:   |   Comments (20)   |   Related: 1 | 2 | 3 | 4 | > Temporal Tables


Temporal tables - not to be mistaken with temporary tables - were introduced as a new feature in SQL Server 2016. Temporal tables, also named system-versioned tables, allow SQL Server to automatically keep history of the data in the table. This tip will introduce you to this feature and will explain how to create a system-versioned table. A second part of the tip will dive deeper into querying temporal tables.


Temporal tables were introduced in the ANSI SQL 2011 standard and has been released as part of SQL Server 2016.

A system-versioned table allows you to query updated and deleted data, while a normal table can only return the current data. For example, if you update a column value from 5 to 10, you can only retrieve the value 10 in a normal table. A temporal table also allows you to retrieve the old value 5. This is accomplished by keeping a history table. This history table stores the old data together with a start and end data to indicate when the record was active.

Temporal table

These are the most common use cases for temporal tables:

  • Audit. With temporal tables you can find out what values a specific entity has had over its entire lifetime.
  • Slowly changing dimensions. A system-versioned table exactly behaves like a dimension with type 2 changing behavior for all of its columns.
  • Repair record-level corruptions. Think of it as a sort of back-up mechanism on a single table. Accidentally deleted a record? Retrieve it from the history table and insert it back into the main table.

Temporal tables or system-versioned tables are an example of an assertion table, meaning that it captures the lifetime of a record based on the physical dates the record was removed or updated. Temporal tables currently do not support versioning, meaning the versioning of records based on logical dates. For example, suppose you have a table keeping product prices. If you update the price at 12PM using an UPDATE statement, the temporal table will keep the history of the old price until 12PM of that day. Starting from 12PM, the new price is valid. However, what if the price change was actually meant to start from 1PM (a logical change)? This means you have to time your update statement perfectly in order to make it work and you should have executed the UPDATE statement at 1PM instead of 12PM. The difference between assertion and version tables, and in more general uni- and bi-temporal tables is discussed in the article Conventional, Uni-Temporal and Bi-Temporal Tables.

Note that temporal tables are not a replacement for the change data capture (CDC) feature. CDC uses the transaction log to find the changes and typically those changes are kept for a short period of time (depending on your ETL timeframe). Temporal tables store the actual changes in the history table and they are intended to stay there for a much longer time.

Creating a system-versioned table

When you want to create a new temporal table, a couple of prerequisites must be met:

  • A primary key must be defined
  • Two columns must be defined to record the start and end date with a data type of datetime2. If needed, these columns can be hidden using the HIDDEN flag. These columns are called the SYSTEM_TIME period columns.
  • INSTEAD OF triggers are not allowed. AFTER triggers are only allowed on the current table.
  • In-memory OLTP cannot be used in SQL Server 2016. Later on, this limitation has been lifted. Check out the documentation for more information.
  • By default, the history table is page compressed.
  • ON DELETE/UPDATE CASCADE is not permitted on the current table. This limitation has also been lifted in SQL Server 2017.

There are also some limitations:

  • Temporal and history table cannot be FILETABLE
  • The history table cannot have any constraints
  • INSERT and UPDATE statements cannot reference the SYSTEM_TIME period columns
  • Data in the history table cannot be modified

For a full list of considerations and limitations, refer to the documentation page Temporal Table Considerations and Limitations.

The following script creates a simple system-versioned table:

CREATE TABLE dbo.TestTemporal
	(ID int primary key
	,A int
	,B int
	,C AS A * B

If you don't specify a name for the history table, SQL Server will automatically generate one of the following structure: dbo.MSSQL_TemporalHistoryFor_xxx, where xxx is the object id of the main table.

The result looks like this:

Temporal table in Object Explorer

The history table has an identical set of columns, but with all constraints removed. It also has its own set of indexes and statistics. Creating your own indexes such as a clustered columnstore index on the history table can greatly improve performance.

Note it is also possible to enable system-versioning on an existing table. Either you already have an existing history table and you just include it in the ALTER TABLE statement, or you create one yourself. For example, if you have an existing type 2 dimension with all the history, you can create a new table with only the current values. This will become the current table and the dimension will become the history table. There is an optional clause DATA_CONSISTENCY_CHECK that allows you to perform a data consistency check to verify if time periods in the history do not overlap. For more information, take a look at ALTER TABLE.

Let's test the functionality of temporal table by inserting data into the table and then modify it.

-- Initial Load
INSERT INTO dbo.TestTemporal(ID, A, B)
VALUES	 (1,2,3)

SELECT * FROM dbo.TestTemporal;

Initial load

Now let's delete one row and update another.

-- Modify Data
DELETE FROM dbo.TestTemporal

UPDATE dbo.TestTemporal
SET A = 5

The main table displays the current state of the data:

Current state

Note that the SysEndTime column is not necessary, as it only displays the maximum datetime2 value.

The history displays the old versions of the different rows and they are properly dated.


Alter the schema of a system-versioned table

In SQL Server 2016, when system-versioning is enabled on a table, modifications on the table are severely limited. These were the allowed modifications:


All other schema modifications were disallowed. However, in SQL Server 2017 many limitations were removed. Check out Changing the Schema of a System-Versioned Temporal Table for more information. In SQL Server 2016 and 2017 it's still not possible to drop a temporal table while system-versioning is enabled.

Error dropping a table

But what when you to alter the schema of the table and the modification is not supported? In this case, system-versioning has to be disabled first:


This command will remove system_versioning and turn the main table and the history table into two regular tables.

Back to normal...

Now you can do any modifications you like on both tables. Make sure they stay in sync and history is still consistent. After the modifications, you can turn system-versioning back on.


The ALTER TABLE documentation has an example on how to modify a temporal table. For an example on how to drop a temporal table, check out How to drop a Temporal Table.

Retention Policy

SQL Server 2017 introduced a new concept to temporal tables: a retention policy. Without any action, history will grow for a temporary tables. Over time, these history tables can grow to large sizes, especially if data is modified often. With retention policies, you can automatically control the growth of a temporal table. For more information, check out the tip Temporal history table retention in SQL Server 2017 and the documentation page Manage historical data in Temporal Tables with retention policy.


Temporal tables are an exciting feature introduced in SQL Server 2016 and expanded in SQL Server 2017. With temporal tables, SQL Server automatically tracks history for the data into a separate history table. Possible use cases are type 2 dimensions in data warehouses, auditing, protection against unwanted deletes and updates and any other example where versioning is required. In a second part of the tip we'll discuss how you can query the temporal table using the new FOR SYSTEM_TIME clause!

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

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, September 3, 2017 - 6:13:20 AM - Dan Back To Top (65793)

Thanks for creating this. It was very informative and well written. 


Thursday, October 6, 2016 - 3:18:30 AM - Koen Verbeeck Back To Top (43501)

Hi Joker,

I just tested it and it's not possible. I got an error saying my history table has one column more than the current table.

Wednesday, September 28, 2016 - 3:29:29 PM - Joker Back To Top (43440)


Is it allowed to use additional columns in History table like host_name()

Tuesday, July 12, 2016 - 3:58:29 AM - Koen Verbeeck Back To Top (41867)

Hi John,

you're certainly right, you can build a durable memory-optimized table using system versioning.

However, when I was writing this article SQL Server 2016 was still in preview (I think CTP 2.1 at the time), and there it was not possible at all. It seems they added the functionality to build system versioning on top of in-memory OLTP tables. So the article is already out of date.

In most of my SQL Server 2016 articles I include a disclaimer stating that the article is writting during the previews and that functionality might be changed or added or even dissappear. It seems I did not include it in this tip yet.

Thanks for your comment!

Friday, June 17, 2016 - 3:59:21 PM - André Back To Top (41714)


"But what when you want to add new columns for example? In order to alter the schema of a temporal table, system-versioning has to be disabled first:"

In SQL Server 2016 RTM you can add and remove columns without disabling the system-versioning.

Friday, June 17, 2016 - 2:52:25 PM - John Nelson #2 Back To Top (41713)

This is great stuff!

Just a point of clarification: you point flat out that "in-memory OLTP cannot be used".  I know how you mean that, but I think it's more nuanced than that.  A durable memory-optimized table can actually be a system versioned table.

I get what you're saying, it's certainly fair to say that a fully in-memory (schema only) system versioned table is not possible, but I think it's also worth mentioning that a durable in-memory (schema_and_data) can be setup so the current data is in-memory and the history data is disk-based.

Wednesday, April 20, 2016 - 8:02:06 AM - Koen Verbeeck Back To Top (41282)

Hi Lee,

it's indeed a reasonable gap. SQL Server doesn't care with which values the row has been updated: an update is an update.
To mitigate this, you could issue a DISTINCT over the history table to remove those extra rows.

Tuesday, April 19, 2016 - 5:45:42 PM - Lee Back To Top (41273)


Thank you for the comprehensive explanation.

One disapointing (but reasonable) gap I found in this new functionality is that it does not recognize if the same update to a row is applied multiple times.  The built in temporal functionality will write a record to the hitory table everytime you apply the update to the primary table.

I usually test for this in my type-2 dimension ETL when applying source data.  If there is no difference between source and destination columns, I do not perform an update or add history to the dimension.

I tested this because I never trust source data to be clean or only change data (even if I develop the source extract!).


Friday, October 9, 2015 - 7:51:47 AM - Jason Clark Back To Top (38850)

Excellent information, i found another helpful blog for the same, see here:

Wednesday, August 12, 2015 - 8:49:29 AM - Koen Verbeeck Back To Top (38431)


CDC is more intended to capture changes in the data using the transaction log and keep those around for a shorter period of time. Typically CDC is used to capture the changes and input those into an ETL process.

Temporal tables are more of an archive table, where it is intended to keep the old versions around for a much longer period of time.
CDC is also not as easy to query as a temporal table. 

Friday, August 7, 2015 - 1:42:20 PM - Eugene Back To Top (38403)

Interesting. What's the difference between CDC and Temporal Tables? Thank you.

Tuesday, August 4, 2015 - 2:39:18 AM - Koen Verbeeck Back To Top (38357)

For those interested, you can find the second part of the introduction (about the query syntax) here:

Tuesday, August 4, 2015 - 2:25:42 AM - rahul rathore Back To Top (38356)

it was really a nice tip.Thanks for sharing this new feature.

Thursday, July 30, 2015 - 8:00:17 AM - Greg Robidoux Back To Top (38321)


This will be discussed in the next tip in this series that comes out next week.


Wednesday, July 29, 2015 - 3:35:37 PM - sdmcnitt Back To Top (38319)

You should show examples of the new query syntax that is used to query this type of table. It is really needed to ask "what record was active at time X" without having to query the main and history tables explicitly. For example FOR SYSTEM_TIME AS OF @SomePointInHistory

Wednesday, July 29, 2015 - 5:07:14 AM - Koen Verbeeck Back To Top (38309)

@ Bodhi and Gustavo: thanks for the nice feedback.

@Pedro: no I haven't done any performance testing yet.

Wednesday, July 29, 2015 - 4:11:58 AM - mahdi loghmani Back To Top (38308)

tanks very helpful

Tuesday, July 28, 2015 - 7:13:57 PM - Pedro Faro Back To Top (38306)

Thanks for your explanation ... I try Temporal tables in MIcrosoft Virtual Labs(SQL 2016)... but have some bugs the crashes SSMS :-)

So far , i use triggers to log my changes and never disappoint me.

have you tested with big tables(UPDATES, DELETEs) ?





Tuesday, July 28, 2015 - 2:10:42 PM - Bodhi Densmore Back To Top (38300)

Thanks for the very well presented post.  I have wanted a feature like this for many years.

Tuesday, July 28, 2015 - 12:26:51 PM - Gustavo Maia Back To Top (38299)

Nice post! Another great resources on this subject are Itzik Ben-gan'a articles on SQL Mag!

get free sql tips
agree to terms