SQL Server 2016 new features-built-in temporal Tables

The boss told me about it before the holiday. 2016 There is an interesting function called temporal Table, today went to see a bit of information to tidy up a bit.

This function looks like a temporary table, but is actually a history table of system maintenance. (It should be a little more convenient to some extent than our manual maintenance history table)

Simply and directly, its interface looks like this (like a T1) after it is created, it is recorded in the table below that has a t1history table.

Then we'll try how to play this function. First, verify that your SQL Server version is 2016. Then we create the table with one such statement

 UseTestGoCreate TableT1 (IDint Identity Primary Key, COl1nvarchar( -), Timefrom datetime2 generated always asrow start, Timeto datetime2 generated always asRowEnd, period forSystem_time (Timefrom,timeto)) with(system_versioning=  on(history_table=Dbo. T1history));

Here I will simply create a table with only the self-increment primary key and one column. Create a system_versioning table. There must be 2 time fields declared as DateTime2, because these 2 fields are required to record the resulting trajectory of the data.

For example, I use a timefrom field to represent the start time of the data, and Timeto indicates the time of failure of the data (for example, if the data is modified and deleted, then the Timeto will record the time of the change and deletion).

Let's test the sample first and then explain

Step 1: New data

Insert  intoT1 (Col1)Values('1111'),('2222'),('3333')Select *  fromT1Select *  fromT1historyid COl1 timefrom timeto----------- -------------------------------------------------- --------------------------- -------------------- -------1           1111                                                .-Ten- -  -: -:30.3598532 9999- A- to  at: -:59.99999992           2222                                                .-Ten- -  -: -:30.3598532 9999- A- to  at: -:59.99999993           3333                                                .-Ten- -  -: -:30.3598532 9999- A- to  at: -:59.9999999(3row affected) ID COl1 timefrom Timeto----------- -------------------------------------------------- --------------------------- --------------------- ------

When new, the data is the latest version, so there is no record in the history table (note that, Timefrom and Timeto these 2 fields will be maintained by the system control, do not need to manually insert, if the display is written to this field, will throw an error. system maintenance of these 2 fields, the time used will be in UTC format time, for our domestic, is the hour-8 operation)

Step 2: Modify the data

UpdateT1SetCol1=Col1+'New' whereId= 2Select *  fromT1Select *  fromT1historyid COl1 timefrom timeto----------- -------------------------------------------------- --------------------------- -------------------- -------1           1111                                                .-Ten- -  -: -:30.3598532 9999- A- to  at: -:59.999999922222New .-Ten- -  -: -:38.0561513 9999- A- to  at: -:59.99999993           3333                                                .-Ten- -  -: -:30.3598532 9999- A- to  at: -:59.9999999ID COl1 timefrom timeto----------- -------------------------------------------------- --------------------------- --------------------- ------2           2222                                                .-Ten- -  -: -:30.3598532  .-Ten- -  -: -:38.0561513

When you modify the data. A history is written to the history table, and the Timeto is set to the currently modified UTC time, and the primary table retains the latest version of the data.

Step 3: Delete

Delete  fromT1whereId= 3Select *  fromT1Select *  fromT1historyid COl1 timefrom timeto----------- -------------------------------------------------- --------------------------- -------------------- -------1           1111                                                .-Ten- -  -: -:30.3598532 9999- A- to  at: -:59.999999922222New .-Ten- -  -: -:38.0561513 9999- A- to  at: -:59.9999999ID COl1 timefrom timeto----------- -------------------------------------------------- --------------------------- -------------------- -------2           2222                                                .-Ten- -  -: -:30.3598532  .-Ten- -  -: -:38.05615133           3333                                                .-Ten- -  -: -:30.3598532  .-Ten- -  -: +:04.3640717

Deleting the data is similar to modifying the mechanism, that is, the main table deletes the row records, but the history table retains a deleted action.

A simple test can be done here. Here are a few more test instructions

1 If you use merge, then the merge operation will correspond to the above increment/delete/change to maintain the version

2 After you have used a version-maintained table, you cannot use the TRUNCATE TABLE operation because the operation does not support

3 drop TABLE, you cannot use the drop TABLES statement directly, you need to first use ALTER table [dbo]. [T1] Set (system_versioning = OFF) To remove the system-maintained version, and then drop the current table and the history table separately

4 I am Dine's ╮ (╯_╰) ╭~ Please other great god guide supplements

5 Thanks to @wy123 's reminder, creating this type of table requires a primary key.

SQL Server 2016 new features-built-in temporal Tables