System-versioned temporal tables were introduced in SQL Server 2016. They provide information about data stored in the table at any point in time by storing an effective dated version of each row rather than only the data that is correct at the current time
You can alter a temporal table to add or change columns, but you must first turn off system versioning. Let’s look at an example.
CREATE TABLE [dbo].[DatabaseSize](
[DatabaseID] [varchar](200) NOT NULL
,[ServerName] [varchar](100) NOT NULL
,[DatabaseName] [varchar](100) NOT NULL
,[SizeBytes] [bigint] NULL
,[SizeMB] AS ([SizeBytes]/(1048576))
,[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL
,[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
,CONSTRAINT PK_DatabaseSize_DatabaseID PRIMARY KEY CLUSTERED (DatabaseID)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[DatabaseSizeHistory]));Temporal tables must have a primary key defined. They also must contain two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END. The statement above creates both the current table and a history table.
The history table has the same schema as the current table, with one difference: the SizeMB column in the history table is not a computed column.
When I initially created the table, I typoed the formula in the computed column. You can’t alter a computed column — you must drop and recreate the column. This is no problem, just turn off system versioning and alter your table, and turn system versioning back on.
But if you try this without specifying your history table, you will find that it stops using the history table created earlier and makes a new history table.
If you specify your history table while turning system versioning back on, you will encounter an error:
Setting SYSTEM_VERSIONING to ON failed because column 'SizeMB' at ordinal 5 in history table 'Test.dbo.DatabaseSizeHistory' has a different name than the column 'ValidFrom' at the same ordinal in table 'Test.dbo.databasesize'.
Temporal tables match the columns between the current table and history table not only by name and data type but by the column’s ordinal position. Dropping and adding the computed column changed its order as it was added to the end of the table.
You can change the column order of a table in the SQL Server Management Studio UI by right-clicking on the table, selecting Design, and then dragging the column to the correct position. Note that you cannot do this on the system-versioned table while system versioning is on. You can either change the column order on the history table, or turn system versioning off and then change the current table.
Once the column orders match, you can turn system versioning back on and specify the the history table.
ALTER TABLE [dbo].[DatabaseSize]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[DatabaseSizeHistory]));This time, the command should complete successfully. You’ll want to drop any unused auto-created history tables before you’re finished.