Temporal Tables

/0 Comments/in /by

I like SQL Server temporal tables for implementing ODS-style tables and change data tracking (CDS) for three main reasons:

  1. SQL Server maintains the system versioning. By contrast, I have witnessed erroneous Start/End dates for pretty much all home grown implementation. Further, SQL Server grains the changes at millisecond level.
  2. There is a clean separation between the current state of data and historical data. SQL Server separates the historical changes to a history table.
  3. You can establish a flexible data retention policy. A retention policy can be established at database or table level. SQL Server take care of purging the expired data.

At the same time, temporal tables are somewhat more difficult to work with. For example, you must disable system versioning before you alter the table. Here is the recommended approach for altering the schema by the documentation:

BEGIN TRANSACTION
ALTER TABLE [dbo].[CompanyLocation] SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE [CompanyLocation] ADD Cntr INT IDENTITY (1, 1);
ALTER TABLE [dbo].[CompanyLocation] SET
(
SYSTEM_VERSIONING = ON
(HISTORY_TABLE = [dbo].[CompanyLocationHistory])
);
COMMIT;

However, if you follow these steps, you will be greeted with the following error when you attempt to restore the system versioning in the third step:

Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined and the LEDGER=ON option is not specified.

Instead, the following works:

ALTER TABLE <system versioned table>  SET (SYSTEM_VERSIONING = OFF) -- disable system versioning temporarily
-- make schema changes, such as adding new columns
ALTER TABLE <system versioned table>  ADD PERIOD FOR SYSTEM_TIME (StartDate, EndDate); -- restore time period
ALTER TABLE [mulesoft].[Employee] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [mulesoft].[EmployeeHistory])) -- restore system versioning