Temporal Table feature in SQL Server

Introduced in SQL Server 2016. Improved in SQL Server 2017.

History tables Traditionally these are manually implemented....

e.g. when updating a row, you insert the old row into a history table you've invented, following your own frail local standard technique.

Problems with manually implemented:

If you use the temporal table feature in sql server, all of that will be handled for you -- a history table created for you, in a consistent manner.

,	[sysStart]	datetime2(7)	generated always as row start hidden not null
,	[sysEnd]	datetime2(7)	generated always as row end  hidden not null
,	period for system_time ([sysStart],[sysEnd])

Note the generated datetime2 columns are always UTC. This can be annoying for users. To get around that, one solution is you could add:

[UpdatedDate] as dateadd(hour, 10, [sysStart])

...this adds a calculated column equivalent to Brisbane time... (we don't have daylight savings so it works for us.)

In SSMS, object explorer, table list, there is a "(System-Versioned)" text next to temporal tables. You expand that table to see the history table "under it". It's wild.

Adding a column is tricky -- because you want it to be inserted as null into the history tables.

Bulk loading

before:

alter table ... set (system_versioning = off)
alter table ... drop period for system time
go

...then perform the bulk...

after:

alter table ... add period for system_time ([sysStart],[sysEnd]);
alter table ... add period for system_time set (system_versioning - on (history_table = ... history table name... ;
go
alter table ... alter column [SysStart] add hidden;
alter table ... alter column [SysEnd] add hidden;
go