Temporal tables of SQL Server 2016 not temporary

Temporal table is an add on to SQL Server 2016 which allows history maintaining. In simple terms it allows us to have a system versioning. For an example if we have a person's salary as 1000 usd as per today and tomorrow if it is updated as 1005 usd in such scenario the temporal table maintains two tables namely the generic one and a history table where the present record will be stored in the normal table and the previous one will be stored in the history table.

Let’s dig in deeper that with an example, First off I will create a employee temporal table with system versioning enabled. In this case even though we are creating a single table it creates two identical tables with one having a history part concatenated to it's name.

***Note : For easier identification the queries are given in italic.

CREATE TABLE [master].dbo.Employee  

(   

  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED  

  , [Name] nvarchar(100) NOT NULL 

  , [Position] varchar(100) NOT NULL  

  , [AnnualSalary] decimal (10,2) NOT NULL 

  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START 

  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END 

  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) 

 )   

 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory)); 

Now we will insert a record to it,

INSERT INTO [master].dbo.Employee([EmployeeID],[Name],[Position],[AnnualSalary])  VALUES(1,'sam','lawyer',1000)

Now we can now use a select statement to check the insertion has happened,

Select * from dbo.Employee

Now to see the functionality of a temporal table I will provide an update to the existing record.

UPDATE [master].dbo.Employee

SET [Position]='Engineer'

where [EmployeeID]=1

Now I will select the record from the employee and employee history table(system generated) to see both current and historical record,

select * from employee

select * from employeehistory

Now the temporal table allows to select the record as per the time we are providing,

Declare @currentdatetime as datetime
SET @currentdatetime = (select getdate() as datetime)

SELECT * FROM employee for system_time as of  @currentdatetime
WHERE EmployeeID =1

Simply there will be a single version of truth according to the time we are providing unless we give the exact same time a new record is overriding the previous one. If we are planning to drop a temporal table It’s not just a simple drop statement. Fist we want to off the system versioning then drop both history and the normal table.

ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF  )

DROP TABLE [dbo].[Employee]

DROP TABLE [dbo].[EmployeeHistory]

In a holistic view temporal table is a great add on to maintain historical data without the intervene of an SSIS package.