SQL 2016 - Temporal Tables - Can you change the schema?

  • Article

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:

 

[sql]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,
PRIMARY KEY CLUSTERED
(
 [colint] ASC
)ON [PRIMARY],
 PERIOD FOR SYSTEM_TIME ([period_start], [period_end])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTestHistory] , DATA_CONSISTENCY_CHECK = ON )
)[/sql]

 

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:

 

Temporal1

 

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:

 

[sql]ALTER TABLE dbo.TemporalTest ADD HaIKnowTSQL VARCHAR(15);[/sql]

 

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:

 

[sql]INSERT INTO TemporalTest (colint,HaIKnowTSQL)
VALUES (20,'TestSuccessful!');
GO
SELECT 'TemporalTest' AS [Table Name], *
  FROM TemporalTest
 WHERE HaIKnowTSQL = 'TestSuccessful!';[/sql]

 

Temporal2

 

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

 

[sql]UPDATE TemporalTest
   SET colint = 21
 WHERE HaIKnowTSQL = 'TestSuccessful!';
GO
SELECT 'TemporalTest' AS [Table Name], *
  FROM TemporalTest
 WHERE HaIKnowTSQL = 'TestSuccessful!';</pre>
SELECT 'TemporalTestHistory' AS [Table Name], *
FROM TemporalTestHistory
WHERE HaIKnowTSQL = 'TestSuccessful!';[/sql]

 

Temporal3

 

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:

 

Temporal4

 

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

https://msdn.microsoft.com/en-us/library/dn935015.aspx

 

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?

Comments

  • Anonymous
    January 28, 2017
    Good stuff Kevin! :)But what about adding a non-NULL column ? What is the official guidance for that ?
    • Anonymous
      January 30, 2017
      You would have to disable and re-enable the Temporal piece of the table because you cannot add a NOT NULL column to a table that has data and if you add it as NULL it will populate the history table with NULLs so you cannot change it to NOT NULL even after populating it with values.