What is a temporal table?Temporal tables give us the possibility to retrieve data from any point in the past and for every data change (Update, Delete, Merge). With temporal table users can recover data from accidental changes (update/delete) as well as audit all changes to data, etc. Temporary table is a new feature in SQL Server available from SQL Server 2016 CTP2 version. A Temporal table is composed of two parts (tables), the first part is the table on which is enabled data tracking and it’s called a System-Versioned table, this table contains current (actual) data, the second part is the table in which is stored any data changes that were made (Update, Delete, Merge) in a System-Versioned table and it’s called History table.
What every temporal table must have?datetime2 columns Every temporal table must have two columns with a datetime2 data type. These columns are used exclusively by the system to record the period of validity for each row whenever a row is changed. Both columns should be defined as NOT NULL. If there are no NOT NULL constraints for datetime2 columns, the system will automatically set them as NOT NULL. If NULL constraints are applied to these columns, then the system will raise an error:
Msg 13587, Level 16, State 1, Line 1 Period column ‘StartTime’ in a system-versioned temporal table cannot be nullable.One datetime2 column must have the GENERATED ALWAYS AS ROW START option set and the second datetime2 column must have the GENERATED ALWAYS AS ROW END option set. The GENERATED ALWAYS AS ROW START column represents the time when the row data became current, basically on an INSERT/UPDATE of the record in the system-versioned temporal table, the system will set current UTC time based on the system clock where the SQL Server instance runs. The GENERATED ALWAYS AS ROW END column represents the time when the row data was no longer current. This column indicates the time when the changes UPDATE/DELETE occur. The system will set the current UTC time. Primary key Also a system-versioned temporal table must have a primary key defined. If the table doesn’t have a defined the primary key, the following error will appear:
Msg 13553, Level 16, State 1, Line 1 System versioned temporal table ‘test.dbo.people’ must have primary key defined.Attributes The table must have a PERIOD FOR SYSTEM_TIME (<startcol>,<endcol>) clause with two datetime2 columns in it. PERIOD FOR SYSTEM_TIME specifies the name of the columns that the system will use to record the period for which a record is valid: The table must also have the SYSTEM_VERSIONING clause. The SYSTEM_VERSIONING is used to set system versioning ON/OFF for the system versioned table: The optional HISTORY_TABLE parameter sets the name of the history table: If name of the history table is not specified, SQL Server will automatically generate a history table name in a format like this:
MSSQL_TemporalHistoryFor_693577509Where the number 693577509 is object_id for the system-versioned table. Here an example of a system-versioned table: After executing this statement, the People temporal table will be created and it will be marked as a System-Versioned table. Inside the People table will be created the PeopleHistory table and it will be marked as History table:
Creating a System-Versioned temporal tableDepending on the scenario, a system-versioned temporal table can be created by modifying an existing table from the database by adding temporal attributes or creating new system-versioned temporal table. There are three ways to create a system-versioned temporal table: Temporal table with an anonymous history table, creating a temporal table with a default history table or creating a temporal table with a user-defined history table. Temporal table with an anonymous history table Basically, when creating this type of temporal table, the only option that is omitted from the code that is shown above is a HISTORY_TABLE parameter: Which means, when this code is executed, SQL Server will automatically generate the history table name and create a history table in the same schema as the current / temporal table. The name of the history table will be like this:
MSSQL_TemporalHistoryFor_613577224Creating a temporal table with a default history table This approach allows the user to pick a much friendlier/meaningful name for history table in the HISTORY_TABLE parameter, rather than to let system to pick a name. The schema (dbo) name is required for the HISTORY_TABLE parameter, otherwise the following error will appear:
Msg 13539, Level 15, State 1, Line 54 Setting SYSTEM_VERSIONING to ON failed because history table ‘PeopleHistory’ is not specified in two-part name format.Creating a temporal table with a user-defined history table Creating user-defined history table is useful when in the history table needs additional options such as an index in order to obtain a better query performance. The history table cannot have a primary/foreign key, triggers, table constraints, unique index. Otherwise, the following error will be displayed:
Msg 13515, Level 16, State 1, Line 45 Setting SYSTEM_VERSIONING to ON failed because history table ‘test.dbo.PeopleHistory’ has custom unique keys defined. Consider dropping all unique keys and trying again.The History and System-Versioned table must have the same number of the columns, the same column names and the same data types in order to a temporal table be created correctly. The below example demonstrates the effect of creating a history table with a different column name (NickName2) than the column name (NickName) in the versioned table. Copy the following code in the query editor and execute: The following error will be raised:
Msg 13524, Level 16, State 1, Line 45 Setting SYSTEM_VERSIONING to ON failed because column ‘NickName2’ at ordinal 4 in history table ‘test.dbo.PeopleHistory’ has a different name than the column ‘NickName’ at the same ordinal in table ‘test.dbo.People’.The code below will successfully create system versioned and user-defined history table: Alter a Non-Temporal Table to be a System-Versioned Temporal Table In order to convert an existing table, create a temporal table, system versioning must be enabled. Let’s create a table and insert some data: Now try to enable system versioning: The following error will appear:
Msg 13510, Level 16, State 1, Line 1 Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.As mentioned earlier, each temporal table must have two columns with datetime2 data type, otherwise the error above will be displayed. Now lets try to execute the below statement with two datetime2 columns: The result will be:
Msg 4901, Level 16, State 1, Line 4 ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘StartTime’ cannot be added to non-empty table ‘People’ because it does not satisfy these conditions.This error occurs, because the system will not allow to add NOT NULL columns in the table which already have data in it. To correct this, let’s add a default value for the datetime2 columns and enable system versioning with default name for the history table: Now, when execute the statement above the table People will become temporal table: The HIDDEN flag will hide these datetime2 columns from appearing in the result of (SELECT *) query: To see datetime2 columns must be included in the SELECT statement list explicitly: The INSERT INTO statement will work without passing values for these columns. The below INSERT statement will execute successfully even though the PERIOD (StartTime,EndTime) columns are omitted from the INSERT statement list Inserting data There are several ways to insert data into a temporal table when the PERIOD columns are not HIDDEN.
- Insert data when the PERIOD columns are omitted from the INSERT statement column list: This statement will execute successfully because the system will automatically generate values for the PERIOD columns.
- Insert data when the PERIOD columns are in the INSERT statement column list: DEFAULT for their value. If the PERIOD columns in the INSERT column list, then need to be specified
- Insert data when the INSERT column list is not specified
Msg 213, Level 16, State 1, Line 16 Column name or number of supplied values does not match table definition.When inserting data into a temporal table the system versioned (current) table will be affected, the history table will be empty: System versioned table: History table: The values in the StartTime column of the system versioned table is the time when the INSERT statement is executed and value in the EndTime will be the maximum datetime2 which is 9999-12-31 23:59:59.9999999 Updating data Let’s execute the below statement, which will update the NickName from Ziva to Mare: Now, retrieve data from temporal and history table: The temporal (current) table is updated and in the StartTime column is set the time when the update statement is executed. The system will automatically insert the record in the history table with the data that were before the update and in the EndTime column of the history table will be inserted the time when the update statement is executed. Let’s try to update the history table PeopleHistory and set NickName from Ziva to Maruska: This will return an error:
Msg 13561, Level 16, State 1, Line 54 Cannot update rows in a temporal history table ‘test.dbo.PeopleHistory’.The only way to update a history (PeopleHistory) table is to set SYSTEM_VERSIONING to OFF in the system versioned (People) table: Since the SYSTEM_VERSIONING is set to OFF the link between system versioned (People) and history (PeopleHistory) tables is gone and both tables will become like any other regular tables: Now let’s try to update StartTime column in the People table: The following error will appear:
Msg 13537, Level 16, State 1, Line 54 Cannot update GENERATED ALWAYS columns in table ‘test.dbo.People’.In order to be available to update PERIOD columns the following statement must be executed: Now execute the UPDATE statement: The following message will appear:
(1 row(s) affected)Deleting data Let’s delete the record from the People table where the PeopleID is 1 This code will delete the record from the temporal table (People) and insert the deleted record in the history table (PeopleHistory): Retrieve data from the temporal and history table: From the image above, it’s clear that the record was deleted from table People and inserted in the history table PeopleHistory with EndTime of the record as the DELETE statement execution time. If executing the delete statement on the history table: Result will be:
Msg 13560, Level 16, State 1, Line 61 Cannot delete rows from a temporal history table ‘test.dbo.PeopleHistory’.This is happening because the update/delete operations are not allowed on the history table as long as SYSTEM_VERSIONING is enabled. To delete history and system versioned table, execute the code below: