It is introduced as a new feature in SQL Server 2016 and also known as system versioned tables which allows us to track the changes or history of data in the table. Normal table gives us only the current data but system-versioned table allows us to query updated and deleted data.
But there is a certain Pre-Requisites are there to create a new temporal table
1. A primary key
2. A non-nullable DATETIME2 column to store the starting effective date
3. A non-nullable DATETIME2 column to store the ending effective date
4. Set the SYSTEM_VERSIONING table attribute to ON
Now let us take a close look on creating a temporal tables
At first we will create a normal table named as client and observe below how it looks like
create database GeoPits
create table clients(
id int not null primary key ,
name varchar(50) ,
project varchar(50)
)
As we can see above the normal table vision .
At second time i will alter the same table with additional columns to place the changes of data in this columns and set system versioning = ON
alter table clients
add SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime);
— Next I will enable the system versioning by running below query
ALTER TABLE dbo.clients
SET (SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = [dbo].[clientshistory]
)
)
We can clearly observe Sql server mentioning the table name clearly as System-Versioned
Now let us insert some values by running the following script
Insert into clients (id,name,project)
values(01,’abc’,’xyz’) ,
(02,’rayyan’,’gundlupet’),
(03,’salman’,’dev’),
(04,’humair_’,’support’);
— And then we get whole inserted data by
Select * from clients
Now let us modify the data by deleting and updating in table
Delete from [dbo].[clients]
where id=1;
update [dbo].[clients]
set name = ‘imran’
where id=4;
By Executing above script command completed successfully.
Finally its time to check the Normal table and History table
By executing below statement we will receive current data
Select * from clients
It will not give us the the data which was deleted or updated.
To overcome this situation temporal table gives us the data which was present before modifying
Select * from clientshistory
In this we can observe that the deleted and updated data has been recorded
By this we can conclude that any data which was deleted by accidently or changed by unknowingly can be traced by this procedure.
We can also identify the present total number of temporal tables enabled in Sql Server by executing below statement
SELECT
OBJECT_NAME(object_id) AS ‘Temporal Table’
,OBJECT_NAME(history_table_id) AS ‘History Table’
FROM sys.tables
WHERE temporal_type = 2
GO
