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 Temporal tables

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

INSERTINTOTemporalTableDemo

VALUES(1001,'Anuj','Singh','Saini',1,default,default),

(1002,'Prince','Kumar','Rastogi',4,default,default),

(1003,'Akhil','Pratap','Singh',1,default,default),

(1004,'Tarun','Kumar','Sinha',1,default,default),

(1005,'Lokesh','Singh','Solanki',1,default,default)

GO

SELECT*FROMTemporalTableDemo

GO

SELECT*FROM[dbo].[MSSQL_TemporalHistoryFor_949578421]

GO

INSERTINTOTemporalTableDemoHiddenCol

VALUES(1001,'Anuj','Singh','Saini',1),

(1002,'Prince','Kumar','Rastogi',4),

(1003,'Akhil','Pratap','Singh',1),

(1004,'Tarun','Kumar','Sinha',1),

(1005,'Lokesh','Singh','Solanki',1)

GO

SELECT*FROMTemporalTableDemoHiddenCol

GO

SELECT*FROM[dbo].[MSSQL_TemporalHistoryFor_709577566]

GO

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 Temporal Tables

Transact-SQL

1

2

3

4

5

6

7

8

UPDATETemporalTableDemo

SETDeptID=2

WHEREEmpID=1004

GO

SELECT*FROMTemporalTableDemo

GO

SELECT*FROM[MSSQL_TemporalHistoryFor_949578421]

GO

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 Temporal Tables

Transact-SQL

1

2

3

4

5

6

DELETEFROMTemporalTableDemoWHEREEmpID=1005

GO

SELECT*FROMTemporalTableDemo

GO

SELECT*FROM[MSSQL_TemporalHistoryFor_949578421]

GO

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:

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.

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.

Share This Story, Choose Your Platform!

Prince Rastogi is working as Principle Database Administrator at Admiral Technologies. Prince started his career working on SQL Server since Yukon. Prince is having almost 7 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor at SQLServerGeeks.com.