SQL Server allows you to convert an existing table into a temporal table.In order to perform this task, you need to make sure that a primary key exists on the table, and if it does not already exist, then you need to create one.
The most important considerations, restrictions and limitations of Temporal Tables are: Please reference to Books Online for complete considerations and limitations list: I have shown how to create temporal and history tables in one DDL script in Listing 1.
As I mentioned earlier, the columns Sys Start Time and Sys End Time with data type datetime2 for both column is required for a temporal table. You’re not obliged to provide a default for those columns, but I would recommend it.
You need to be aware of some specific details when converting the in-memory optimized table to the system-versioned table: The DDL for creating a new in-memory optimized table with the temporal table options is very close in its syntax to a traditional disk-based table.
The in-memory optimized table syntax has the WITH block to set MEMORY_OPTIMIZED and DURABILITY properties initially.
For example, let’s set the existing table Department_Exist to a temporal table. Refresh the Table icon to see the result, as in Figure 2.
Although the process of converting an In-Memory Optimized OLTP table to a system-versioned table is similar, there are some differences that we need to cover and demonstrate in this section.
The temporal table’s SYSTEM_TIME period columns (for example Sys Start Time and Sys End Time) enables the mechanism to query data for a different time slice more efficiently.
The updated or deleted data moves into the “historical” table, whilst the “parent” table keeps the latest row version for updated records.
ANSI SQL 2011 first specified a temporal table as a database feature and this is now supported in SQL Server.
The most common business uses for temporal tables are: In the dark days before SQL Server 2016 was introduced, the data-logging mechanism had to be established explicitly in a trigger.
Right click on the top left corner of the table again. Below you can see the new result set available now for editing!