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, 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 ) )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:
ALTER TABLE dbo.TemporalTest ADD HaIKnowTSQL VARCHAR(15);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!'); GO SELECT 'TemporalTest' AS [Table Name], * FROM TemporalTest WHERE HaIKnowTSQL = 'TestSuccessful!';
UPDATE TemporalTest SET colint = 21 WHERE HaIKnowTSQL = 'TestSuccessful!'; GO SELECT 'TemporalTest' AS [Table Name],* FROM TemporalTest WHERE HaIKnowTSQL = 'TestSuccessful!'; SELECT 'TemporalTestHistory' AS [Table Name],* FROM TemporalTestHistory WHERE HaIKnowTSQL = 'TestSuccessful!';
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.