Add a column at a specific position in system versioned temporal table

Question

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?

Answer 1

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.