0

I need to add a column to a temporal table. The Microsoft documentation only suggests appending the new column using the following code:

ALTER TABLE dbo.Department
    ADD TempColumn INT;

However, this would cause the column to appear below the audit columns, including the mandatory system versioning columns 'ValidFrom' and 'ValidTo'. Whilst this is not an issue in terms of performance, all of our non-system versioned tables keep the audit columns as the last columns.

I have tried setting system versioning off but can't work out how to make the changes as I would with a normal user table whilst preserving the data in the history table.

SSMS allows you to create the code for modifying tables using the 'Design' option but this is also not available for temporal tables. I would usually use code that is similar to the 'Design' option output for adding a column where a new table is created with the new column and a suffix in the table name, the old table is dropped and the new table is renamed.

How can I do this for temporal tables please?

1 Answer 1

4

Once you have fully turned versioning off for that table, the base table and the history table are just two normal tables. So you can:

  1. Turn off history
  2. Add the column into the base table in the place you want (you'll need to use the "new table, copy data, drop old, rename new" approach as you can't add columns in a particular place otherwise)
  3. Repeat this column add for the history table
  4. Recreate the versioning relationship between the two tables

But I would recommend avoiding the extra complication and just adding the new column to the end unless this is causing you a problem beyond aesthetics.

1
  • Step 1: To "fully" turn off versioning including "Turn off history" do I SET (SYSTEM_VERSIONING = OFF). Step 2: The new table is to be created without system versioning? Step 4: To "recreate the versioning relationship" do I add period for system_time then SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.DepartmentHistory))? Thanks
    – Fletch
    Mar 12, 2021 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.