SQL Server 2016 – DML Operations on Temporal Table

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:

------------------ Step 1: Create temporal database -----------------
CREATE DATABASE TEMPORAL;
GO
------------------ Step 2: Create temporal table -----------------
USE [TEMPORAL]
GO
CREATE TABLE TemporalTableDemo
(
	EmpID BIGINT NOT NULL PRIMARY KEY, --One Primary Key is mandatory
	FName VARCHAR(100) NOT NULL,
	MName VARCHAR(100) NOT NULL,
	LName VARCHAR(100) NOT NULL,
	DeptID INT NOT NULL,
	-- two columns with datatime2 data type specified as GENERATED ALWAYS AS ROW START/END
	LifeStart DATETIME2 GENERATED ALWAYS AS ROW START, 
    	LifeEnd DATETIME2 GENERATED ALWAYS AS ROW END,
    	PERIOD FOR SYSTEM_TIME (LifeStart, LifeEnd)
)
WITH (SYSTEM_VERSIONING=ON)
GO
CREATE TABLE TemporalTableDemoHiddenCol
(
	EmpID BIGINT NOT NULL PRIMARY KEY, --One Primary Key is mandatory
	FName VARCHAR(100) NOT NULL,
	MName VARCHAR(100) NOT NULL,
	LName VARCHAR(100) NOT NULL,
	DeptID INT NOT NULL,
	-- two columns with datatime2 data type specified as GENERATED ALWAYS AS ROW START/END HIDDEN
	LifeStart DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN, 
    	LifeEnd DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
    	PERIOD FOR SYSTEM_TIME (LifeStart, LifeEnd)
)
WITH (SYSTEM_VERSIONING=ON)
GO

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:

INSERT INTO TemporalTableDemo
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 * FROM TemporalTableDemo
GO
SELECT * FROM [dbo].[MSSQL_TemporalHistoryFor_949578421]
GO
INSERT INTO TemporalTableDemoHiddenCol
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 * FROM TemporalTableDemoHiddenCol
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:

UPDATE TemporalTableDemo
SET DeptID=2
WHERE EmpID=1004
GO
SELECT * FROM TemporalTableDemo
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:

DELETE FROM TemporalTableDemo WHERE EmpID=1005
GO
SELECT * FROM TemporalTableDemo
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:

ALTER TABLE TemporalTableDemo 
SET (SYSTEM_VERSIONING = OFF)
GO  
INSERT INTO [MSSQL_TemporalHistoryFor_949578421]
VALUES (1002,'Prince','Kumar','Rastogi',4,getdate(),DATEADD(dd,1,getdate()))
GO
UPDATE [MSSQL_TemporalHistoryFor_949578421]
SET DeptID=1
WHERE EmpID=1004
GO
SELECT * FROM TemporalTableDemo
GO
SELECT * FROM [MSSQL_TemporalHistoryFor_949578421]
GO

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.

ALTER TABLE TemporalTableDemo 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MSSQL_TemporalHistoryFor_949578421], DATA_CONSISTENCY_CHECK = ON))
GO

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.

DELETE FROM [MSSQL_TemporalHistoryFor_949578421]
WHERE EmpID=1002
GO
SELECT * FROM TemporalTableDemo
GO
SELECT * FROM [MSSQL_TemporalHistoryFor_949578421]
GO
ALTER TABLE TemporalTableDemo 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MSSQL_TemporalHistoryFor_949578421], DATA_CONSISTENCY_CHECK = ON))
GO

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