SQL 2016 - Temporal Tables - Can you change the schema?
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:
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]
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]
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:
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.
- Anonymous