Temporal tables in SQL Server are a great way to use when you need to keep track of data row versions over time. For example for auditing for keeping track of any data changes, creating slowly-changing dimensions or protecting data in case of accidental data loss.
I created a system-versioned temporal table in a SQL database and wanted to alter the schema for this table. A temporal table is created using 2 tables; a table containing current records and a table containing the historic records. To alter the schema, we need to alter the schema for both tables.
To be able to alter the schema we need to do the following steps:
- Disable system versioning on the temporal table
- Alter the schema of the current table
- Alter the schema of the history table
- Enable system versioning on the temporal table
We can do this by using the SQL statements below. By using a transaction statement, if something failes, the changes are automatically rolled back.
BEGIN TRAN
ALTER TABLE [current_schema].[MyTable] SET (SYSTEM_VERSIONING=OFF);
ALTER SCHEMA [new_schema] TRANSFER [current_schema].[MyTable];
ALTER SCHEMA [new_schema] TRANSFER [current_schema].[MyTable_History];
ALTER TABLE [new_schema].[MyTable]
ALTER TABLE [current_schema].[MyTable] SET (SYSTEM_VERSIONING=OFF);
ALTER SCHEMA [new_schema] TRANSFER [current_schema].[MyTable];
ALTER SCHEMA [new_schema] TRANSFER [current_schema].[MyTable_History];
ALTER TABLE [new_schema].[MyTable]
SET (SYSTEM_VERSIONING=ON (HISTORY_TABLE = [new_schema].[MyTable_History]));
COMMIT;
COMMIT;