We have a system that is currently used by customers that have versions of SQL from 2008 R2 all the way to 2019. We want to start introducing, in a limited way, Temporal Tables, even for customers who are using SQL versions earlier than 2016.

Our plan is to create the necessary History tables, which are identical to the respective root tables, except for two main differences:

1) 2 additional columns, SysStartTime datetime2 NOT NULL DEFAULT SYSUTCDATETIME() and SysEndTime datetime2 NOT NULL DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ), added to the new History table. For 2016 usage, these columns would also be added to the root table, but we're using triggers for the pre-2016 version, and we can't be modifying the SysStartTime/SysEndTime columns in the middle of the trigger.

2) No primary key, foreign keys, or other constraints on the History table, although from what I've read, indexes are permitted, though these likely would need to be dropped before converting to SQL Server 2016.

Then, for the root table, create an Update/Delete trigger that will populate the history table when data is updated or deleted.

My questions are:

1) Will this methodology be able to be migrated without loss of data when a client upgrades to SQL 2016 or newer?

2) What would those migration steps be?

I believe we would need to have two schemas for our database, one for clients pre-2016 that uses the home-grown pseudo-temporal tables, and one for 2016 clients (or newer) that uses the built-in functionality, with a series of scripts that can take them from one form to the other, but with a solid upgrade plan, I believe this would be manageable.

  • You can do this with a single schema for both. See docs.microsoft.com/en-us/sql/relational-databases/tables/… – Martin Smith Feb 21 at 23:03
  • The only problem with that is that the "ADD PERIOD FOR SYSTEM_TIME" ALTER TABLE option is not valid in SQL Server 2008, so we are unable to use a single schema for both. However, the instructions are useful for building the SQL Server 2016-compatible tables, as all tables, today, need to be migrated. – Dan Feb 24 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.