Temporal Tables in SQL Server 2016

Temporal Table is a new type of user defined table introduced in SQL Server 2016. Temporal table are tables that SQL Server automatically tracks the full history of the data changes into a separate history table. This gives you the ability to know what your data was at any point in time.

Temporal Tables provide the built-in support for tracking the full history of the data and also provides querying construct FOR SYSTEM_TIME to query historic data at any point in time or for a period of time. Temporal Table in SQL Server can be used in a number of different senerios. I can think of auditing or slow changing dimensions. What I really see the benefit of Temporal Tables is the replacement of that table/trigger solution that all DBA's have come up with in the past. :)

Considerations:

  1. Table needs to have a Primary Key. If table does not have the primary key it will raise an error.
  2. Table needs to have two DATETIME2 columns representing the start and end of the row's validity period.
  3. Start/End columns should be NOT NULL

Step 1 - Create the Temporal Table

In this step we will create the Benefits table in the HumanRecoures schema and turn on versioning for a history table. What is important to remember is that the datetime2 data type must be used when creating temporal tables. 
CREATE TABLE HUMANRESOURCES.BENEFITS 
(  
  ENTITYID INT NOT NULL PRIMARY KEY CLUSTERED,
  PROVIDERNAME NVARCHAR(100) NOT NULL, 
  EMPLOYEEID INT NOT NULL,
  MEDICALELECTION VARCHAR(100) NOT NULL,
  DENTALELECTION VARCHAR(100) NOT NULL,
  VISIONELECTION VARCHAR(100) NOT NULL,
  ELECTIONYEAR VARCHAR(4) NOT NULL,
  STARTTIME DATETIME2 GENERATED ALWAYS AS ROW START
            NOT NULL,
  ENDTIME   DATETIME2 GENERATED ALWAYS AS ROW END
            NOT NULL,
  PERIOD FOR SYSTEM_TIME (STARTTIME, ENDTIME)   
) 
WITH(
	SYSTEM_VERSIONING= ON 
	(HISTORY_TABLE=HUMANRESOURCES.BENEFITSHISTORY))
GO
					









					

					


Step 2 - Insert Sample Data

In this step, we are going to insert some sample data.  The sample data is the current employees elections for the year 2017.  Employee number 2, only has benefits for himself in 2017.





					
INSERT INTO [HUMANRESOURCES].[BENEFITS] ([ENTITYID]
	,[PROVIDERNAME],[EMPLOYEEID],[MEDICALELECTION]
	,[DENTALELECTION],[VISIONELECTION],[ELECTIONYEAR])
    SELECT 1,'HEALTHCARE.GOV',1,'EMPLOYEE+FAMILY'
		,'EMPLOYEE+FAMILY','EMPLOYEE+FAMILY','2017'
GO
INSERT INTO [HUMANRESOURCES].[BENEFITS] ([ENTITYID]
	,[PROVIDERNAME],[EMPLOYEEID],[MEDICALELECTION]
	,[DENTALELECTION],[VISIONELECTION],[ELECTIONYEAR])
    SELECT 2,'HEALTHCARE.GOV',2,'EMPLOYEE'
	,'EMPLOYEE','EMPLOYEE','2017'
GO
INSERT INTO [HUMANRESOURCES].[BENEFITS] ([ENTITYID]
	,[PROVIDERNAME],[EMPLOYEEID],[MEDICALELECTION]
	,[DENTALELECTION],[VISIONELECTION],[ELECTIONYEAR])
    SELECT 3,'HEALTHCARE.GOV',3,'EMPLOYEE+FAMILY'
	,'EMPLOYEE+FAMILY','EMPLOYEE+FAMILY','2017'
GO

SELECT * FROM [HUMANRESOURCES].[BENEFITS]
					












Step 3 - Update Data in Benefits Table

In this step, our employee #2 was married in 2017 and will change their benefits to Employee+Spouse.  This will create a record in the BenefitsHistory table that will be the vaild history record for this row.


After the update, a record is inserted in the BenefitsHistory table.  You will notice the EndTime of the record in the BenefitsHistory table matches the Startime of the Benefits table.





					
UPDATE [HUMANRESOURCES].[BENEFITS]
    SET ELECTIONYEAR = '2017'
        ,MEDICALELECTION = 'EMPLOYEE+SPOUSE'
        ,DENTALELECTION = 'EMPLOYEE+SPOUSE'
        ,VISIONELECTION = 'EMPLOYEE+SPOUSE'
WHERE EMPLOYEEID = 2
GO

GO
SELECT * FROM [HUMANRESOURCES].[BENEFITS]
GO
SELECT * FROM [HUMANRESOURCES].[BENEFITSHISTORY]
GO
					









Step 4 - Update Data in Benefits Table, AGAIN!

To continue with our example, our employee #2 has had a child and now we need to update the election to Employee + Family.  This will insert a record into the history table.


Again, notice the Endtime will equal the StartTime of the active record.





					
UPDATE [HUMANRESOURCES].[BENEFITS]
    SET ELECTIONYEAR = '2017'
        ,MEDICALELECTION = 'EMPLOYEE+FAMILY'
        ,DENTALELECTION = 'EMPLOYEE+FAMILY'
        ,VISIONELECTION = 'EMPLOYEE+FAMILY'
WHERE EMPLOYEEID = 2
GO
SELECT * FROM [HUMANRESOURCES].[BENEFITS]
GO
SELECT * FROM [HUMANRESOURCES].[BENEFITSHISTORY]
GO