Temporal Tables

This page details the support for temporal tables in AbaPerls.

Contents:
   Introduction
   Basic Requirements
   Files for Temporal Tables
   Update Scripts Generated by DBUPDGEN
   Changes To/From Temporal
   Tables with Only SYSTEM_PERIOD

Introduction

If you say:

CREATE TABLE temporalmain
     (alfa      int          NOT NULL,
      beta      nvarchar(30) NOT NULL,
      gamma     date         NOT NULL,
      starttime datetime2(3) GENERATED ALWAYS AS ROW START HIDDEN,
      endtime   datetime2(3) GENERATED ALWAYS AS ROW END,
      PERIOD FOR SYSTEM_TIME (starttime, endtime),
      CONSTRAINT pk_temporalmain PRIMARY KEY (alfa)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.temporalhist))

SQL Server will create both the table temporalmain and the table temporalhist. It will also create a clustered index ix_temporalhist on the history table. SQL Server also permits you to specify an existing table, which must have the same schema as the main table. In this case, SQL Server will not add any index to the history table.

Basic Requirements

To be able to use temporal tables with AbaPerls, the database needs to have at least version L1.0.0400 of the ABAPERLS subsystem, or else you will get an error.

When you specify SYSTEM_VERSIONING = ON, SQL Server supports three subclauses: HISTORY_TABLE, DATA_CONSISTENCY_CHECK, HISTORY_RETENTION_PERIOD (the latter from SQL 2017 and on.). The following applies for these options with regards to AbaPerls:

HISTORY_TABLE

AbaPerls requires that you specify this option to explicitly name the history table. SQL Server permits to leave out this subclause and in this case it will generate a name for the history table, but AbaPerls does not agree to this. (Which is in consistency with that AbaPerls requires you to name constraints.) Note that SQL Server requires the table to be named in two-part format, with schema and database. AbaPerls in its turn requires the schema to be dbo, since it does not suppport schemas.

DATA_CONSISTENCY_CHECK

AbaPerls gives a blind eye to this option. If you specify it in the .tbl file for a temporal table, AbaPerls will include it the CREATE TABLE command. However, in situations when AbaPerls turns SYSTEM_VERSIONING on and off to be able to carry out its operations, AbaPerls never specifies this option, which means that SQL Server will always perform a check that the data is consistent in these situations.

HISTORY_RETENTION_PERIOD

AbaPerls does not permit this option in the CREATE TABLE statement. There are two reasons for this. One is that it is likely that you will want to have different retention settings in different databases and sites. The second is that this option presents some challenges for AbaPerls, as SQL Server in this case reuqires that the history table has a clustered index with certain characteristics. (This is difficult for AbaPerls, because when building a database with DBBUILD, .ix files are loaded after all .tbl files have been loaded.) If you want to set up a retention period in the install kit you ship, you can do this with an ALTER TABLE statement in a .postsql file. (Which you must embed in dynamic SQL, since ALTER TABLE is not permitted in .postsql files.) When running an upgrade script generated by DBUPDGEN, the setting in the database will be respected, see further below about update scripts.

Files for Temporal Tables

Taking the example above, you will have a temporalmain.tbl, but what about temporalhist? AbaPerls permits you to have a temporalhist.tbl, but you don't have to create such a file. That is, you can choose whether to create the history table yourself, or let SQL Server do it for you (but see the discussion below about load order). There are three possible reasons why you would have a .tbl file for the history table.

You are transforming a pair of existing tables to a temporal table.
You want to specify a table option with ALTER TABLE or sp_tableoption for the history table.
Without a temporalhist.tbl, AbaPerls may not be able to respect storage settings set locally in the database, for instance a different filegroup for the history table. Since this is something you cannot always forsee in advance, the recommendation is to always have .tbl files for history tables.

When you load temporalmain.tbl for an existing table, and the schema in the file is different from the table in the database, AbaPerls will attempt produce ALTER TABLE statements as for any other table, as discussed on the file-load page, to bring the table in the database to be in conformance with the file. These statements will also affect the history table; this is something SQL Server handles for you.

If you load temporalhist.tbl for an existing history table, AbaPerls applies the same procedure, but most ALTER TABLE statements will fail, since SQL Server does not permit changing columns in history tables directly. The only differences that can be applied are table options. Keep in mind, that if the table and the file match each other, the load will complete successfully. That is, when you have made changes to a temporal table, you should first load temporalmain.tbl to apply the changes, and then load temporalhist.tbl to verify that the file is in sync and will not cause load errors.

Note that the load order in DBBUILD will determine in which order files are loaded. In this particular example, DBBUILD will load temporalhist.tbl before temporalmain.tbl, because it loads tables in alphabetic order. But if you instead use the names temporal and temporalhist, temporal.tbl will be loaded before temporalhist.tbl, and thus, SQL Server will create the history table together with the main table. In most cases, this is of little practical importance, because temporalhist.tbl will still load without errors as long as it matches temporal.tbl. Would you need to set a table option for the history table that cannot be applied to an existing table, you would have to name it to come alphabetically before the main table.

The same discussion applies to update scripts generated by DBUPDGEN, but here you have the possibility to edit the scripts after generation to alter the order.

You can also have a temporalhist.ix if you want to set up additional indexes for the history table, or you want have a different clustered index from the default, for instance a clustered columnstore index. You can have a temporalhist.ix, even if you don't have a temporalhist.tbl. Note: If you decide to have a different clustered index for your history table, you are very much recommended to use the same name as SQL Server uses when it creates the history table implicitly, that is, ix_temporalhist in this example. Else you can run into error messages in DBBUILD or update scripts, depending on the order in which files are loaded.

When it comes to other file types, .fkey etc, they are not applicable to history tables, since SQL Server does not support foreign keys, triggers or direct inserts into history tables.

If you load temporalmain.tbl with the ‑nocreate option to drop the temporal table, this will drop both temporalmain and temporalhist. On the other hand, if you load temporalhist.tbl with ‑nocreate, AbaPerls will not drop anything, but only print out an informational message.

Update Scripts Generated by DBUPDGEN

When you make changes to a temporal table, DBUPDGEN will add more code to the section for the table in the update script than is shown on the page for DBUPDGEN. Here is a description of these differences. In the initial part, we find:

$tbl = 'temporalmain';
$old_tbl = aba_move_aside($tbl);
my $histtbl = '[dbo].[temporalhist]';
my $old_histtbl = '[dbo].[temporalhist]';
$old_histtbl = aba_move_aside($histtbl);

That is, the update script defines two variables for the history table, one for the new version and one for the old. The name is always in two-part format, and always in brackets. The old version of the history table is moved to AbaPerls$SideSchema, just like the main table. Next we have:

abasqlfile('temporalhist.tbl');
abasqlfile('temporalmain.tbl');
abasqlfile('temporalhist.ix');
abasqlfile('temporalmain.ix');
abasqlfile('temporalmain.tri');

That is, if there is a temporalhist.tbl, the update script will load it before the main table. This permits AbaPerls to pick up storage settings for the table and the clustered index which have been set locally. For instance, a site may have decided to put the history table on a specific filegroup. If you don't have a temporalhist.tbl, it will not be possible for AbaPerls to honour these local settings, since table and index will be created when temporalmain.tbl is loaded.

Note that the files for the history table will be included in the upgrade script, even if they have not been updated themselves. (They are handled in the same manner as .tri and other table files.)

Next, just before the batch to copy the data for the main table, there is this call:

suspend_temporal($tbl, $old_tbl, $histtbl);

suspend_temporal will turn off SYSTEM_VERSIONING for the main table and also drop the period for SYSTEM_TIME. This is required to make it possible to load data into the period columns which have GENERATED ALWAYS in temporalmain.tbl file. As long as the period is there, SQL Server does not agree to these columns to be set explicitly.

Next part of interest is this:

$stat *= abasql($fkey) if $stat;

if ($stat) {
   $batchsize = 5000000;
   $batchcol  = 'endtime';

   setup_for_datamove($histtbl);

   $sql = <<SQLEND;

That is, provided that the reload of the main table completed successfully, there is a second round of data shuffling, this time for the history table. DBUPDGEN always sets the batch column to the the column that is the end of the period for SYSTEM_TIME in temporalmain.tbl, since the default index that SQL Server creates has the end column as the leadning column. If you have set up a different clustered index for your history table, you will need to change this column. That is, DBUPDGEN makes no attempt read temporalhist.ix to find out.

After the reload loop, there is one more call before the end of data shuffling:

$stat *= restore_temporal($tbl, $old_tbl, $histtbl) if $stat;
#----------- End of data shuffling -------------

restore_temporal restores PERIOD FOR SYSTEM_TIME and enables SYSTEM_VERSIONING = ON for the main table. On SQL 2017 and later, it also retrieves the current setting of HISTORY_RETENTION_PERIOD for the old table and copies this setting to the new table.

The end of the update part is this:

$stat *= abasql("ALTER TABLE $old_tbl SET (SYSTEM_VERSIONING = OFF)") if $stat;
drop_table_after_datamove($histtbl, $old_histtbl) if $stat;
drop_table_after_datamove($tbl, $old_tbl) if $stat;
}   # End of update of temporal test

That is, the two tables in the AbaPerls side schema are dropped, and for this to be possible, the update script, disables SYSTEM_VERSIONING for the old main table first.

We can note the else part, that is, the part that runs when abasqlfile for temporalmain.tbl completes successfully:

else {
   abasqlfile('temporalhist.tbl');
   abasqlfile('temporalhist.ix');
   abasqlfile('temporalmain.ix');
   abasqlfile('temporalmain.tri');
}

If you have not kept temporalhist.tbl in sync this will be detected here. Also, this ensures that the files for the history table are included when you create an install kit with ‑noexec ‑get.

If you have changed both temporalmain.tbl and temporalhist.tbl, there will not be any section for the history table in the update script. If you for some reason would change only the .tbl file for the history table, there will be a section in the update script which will look just as if it is was a regular table. That is, DBUPDGEN does not recognise that temporalhist is history table. However, if the data-shuffling part is entered, it will always fail, when AbaPerls attempts to drop the original history table from the side schema. (Because SQL Server does not agree to drop any of the tables in a temporal pair.)

If you delete temporalhist.tbl from version control, but make no other changes, this file will appear in OBSOLETE-FILES, but as discussed above for ‑nocreate, nothing will actually happen. AbaPerls will print an informational message that the table is not being dropped.

If you delete temporalmain.tbl from version control, the file will appear in the OBSOLETE-FILES section, which will result in both the main table and the history table to be dropped, even if you have retained temporalhist.tbl. If you want to retain the data in the history table, you will need to make manual rearrangements in the update script.

Changes To/From Temporal

If you take an existing table and make it into a temporal table, AbaPerls will attempt to implement this with ALTER TABLE. To a great extent, this is not much different from when creating a new temporal table. An update script generated by DBUPDGEN will include calls to suspend_temporal and retsore_temporal to permit reloading of the data. There will not be any reload part for the history table, though.

If you want to make a temporal-table pair of two existing tables, both having data, you will have to review and test the update script. AbaPerls does not attempt to handle this situation. It is possible that an update script from DBUPDGEN will not have a reload section for the history table.

If you remove SYSTEM_VERSIONING = ON for a temporal table, AbaPerls will attempt to implement this with ALTER TABLE, and as part of this, it will drop the history table, no matter if there is a .tbl file for the history table or not. That is, if you want to retain the data in the history table, you will need to make your own arrangements.

Tables with Only SYSTEM_PERIOD

SQL Server permits that you define PERIOD FOR SYSTEM_PERIOD without setting up SYSTEM_VERSIONING (but not vice versa). This is supported by AbaPerls. In upgrade scripts generated by DBUPDGEN, there will be calls to suspend_temporal and restore_temporal to drop the period to permit the period columns to be reloaded, and then to restore the period after the reload loop.