Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
SQL 2016 - Temporal Tables - Can you change the schema?
Published Mar 15 2019 01:03 PM 2,316 Views

First published on MSDN on Dec 21, 2016
I was recently asked if after you setup a Temporal Table, can you modify the schema?  It was a great question because if you cannot change the schema, then you would have to drop and recreate the table every time you need to change the schema which means you would potentially lose all the history.  Of course, there are workarounds to preserve that history like using views to combine more than one history table, but that can become a maintenance nightmare on tables that are changed frequently (like those in development where you want use Temporal Tables to track data modifications for troubleshooting).

Testing to find out if we can change the schema of a temporal table is pretty easy.  First we need to create a temporal table:

CREATE TABLE [dbo].[TemporalTest](
 [colint] [int] NOT NULL,
 [period_start] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
 [period_end] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 [colint] ASC
 PERIOD FOR SYSTEM_TIME ([period_start], [period_end])

Next, let’s see if we can use the Table Designer in SSMS.  Here is what we see when we right click on our temporal table:

Hmmm, the Design option is missing, this makes me think that you cannot change the schema.  Let’s see what happens if we use TSQL to add a column:


When you run the above command, to my surprise, it showed it completed successfully!  Let’s add some data and see if it’s populating:

INSERT INTO TemporalTest (colint,HaIKnowTSQL)
VALUES (20,'TestSuccessful!');

SELECT 'TemporalTest' AS [Table Name], *
FROM TemporalTest
WHERE HaIKnowTSQL = 'TestSuccessful!';

So far so good, but did we break the history table?  Let’s modify the data and find out:

UPDATE TemporalTest
SET colint = 21
WHERE HaIKnowTSQL = 'TestSuccessful!';

SELECT 'TemporalTest' AS [Table Name],*
FROM TemporalTest
WHERE HaIKnowTSQL = 'TestSuccessful!';

SELECT 'TemporalTestHistory' AS [Table Name],*
FROM TemporalTestHistory
WHERE HaIKnowTSQL = 'TestSuccessful!';

Success, we can modify the schema of a temporal table!

One limitation however, is that you cannot drop the primary key which means you cannot change it.  When I tried to drop it, I received this error:

For more information on Temporal Tables, please check out this MSDN article:

Links to other posts in this series

SQL 2016 – Temporal Tables – What are they and how do you set one up?

SQL 2016 – Temporal Tables – How do you drop a Temporal Table?

Version history
Last update:
‎Apr 28 2020 01:33 PM
Updated by: