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?