Hello Friends,

In my previous blog posts, we have seen multiple ways for the creation of temporal table. You can go to those posts by clicking on below links:

SQL Server 2016 – Temporal Tables Part 1

SQL Server 2016 – Temporal Tables Part 2

SQL Server 2016 – Temporal Tables Part 3

Today, we will take a look on the working of DML operations on temporal table. We will create two tables, one with the normal temporal structure while another with hidden columns:

Now let’s try to insert a few records in the system version tables. For normal structure (without the hidden columns) we have to specify the default keyword. If you have specified these columns as hidden then no need to specify default keyword during insert statements as shown below:

SQL Server 2016 – DML Operations on Temporal Table Insert Operation

After insertion, you can see that the current table has all the records while history table does not have any records because there is no change on existing data on current table. Now let’s try to update the records:

SQL Server 2016 – DML Operations on Temporal Table Update Operation

Now you can see that the history table contains a record which has the values before update changes. Now we will check the impact of the Delete statement on temporal tables:

SQL Server 2016 – DML Operations on Temporal Table Delete Operation

You can see that deleted record has been moved from current table to history table. Can we perform DML operations over the history table due to some requirement? Yes, we can do that, but we have to turn the system versioning off before any DML operation on history table. We will insert one new record and will update one existing record:

SQL Server 2016 – DML Operations on Temporal Table DML Operation on History

In the above output image you can see the inserted and updated records in the history table. After completion of DML operation over the history table, turn the versioning ON again. Always use data consistency check setting on while doing this. This will check the consistency of data between both the tables – current and historical.

Msg 13543, Level 16, State 0, Line 17

Setting SYSTEM_VERSIONING to ON failed because history table ‘TEMPORAL.dbo.MSSQL_TemporalHistoryFor_949578421’ contains invalid records with end of period set to a value in the future.

You can see that we are getting consistency error due to the record that we have inserted for lifeend column. The value that we have inserted for lifeend column for the next day (future value), which is not possible for system versioning. I am going to delete that inserted record from history table and will enable the system versioning.

SQL Server 2016 – DML Operations on Temporal Table DML Operation on History 2

You can see that it is working fine now. It is always recommended to specify data consistency check on, if you are trying to enable the system versioning on again.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook