DEV Community

loading...

Alter SQL Server Temporal Table

Joe Enos
Software developer/architect, mostly working with .NET and SQL Server.
・1 min read

In a previous article I walked through creating a temporal table in SQL Server, which gives you the ability to track all changes without resorting to triggers or custom code.

When you have a temporal table, it's an extra couple of steps to alter it - generally adding new columns.

First, disable versioning on the table:

ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = OFF);
GO
Enter fullscreen mode Exit fullscreen mode

Next, apply your change to both the regular and history table:

ALTER TABLE dbo.MyTable_Hist ADD SomeNewColumn VARCHAR(10) NULL;
GO
ALTER TABLE dbo.MyTable ADD SomeNewColumn VARCHAR(10) NULL;
GO
Enter fullscreen mode Exit fullscreen mode

Finally, re-enable versioning:

ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.MyTable_Hist, DATA_CONSISTENCY_CHECK=ON);
GO
Enter fullscreen mode Exit fullscreen mode

If you're using a database project in Visual Studio, these scripts will be written for you - all you need is to add the column to both tables, and generate a publish script.

Discussion (0)