Temporal Tables are a new feature of an SQL Server 2016. Temporary and Temporal tables are different and one should not be confused with both the terms. Temporal Table is also called as System Versioned tables.
Temporal tables give application developers to view the state of data at a point in time.
A Temporal table allows us to query updated and deleted data, while a normal table can only return the current data. For example, if a column value in a table is updated from 7 to 10, By querying normal table we will get 10 . A temporal table also allows us to retrieve the old value 7. This is accomplished by keeping a history table. This history table stores the old data together with a start and end date(time) to indicate when the record was active.
Use case for this feature would be to find the data values in a past date. This can be easily configured with the help of SQL Server 2016.
When we are enabling System Version for a table an extra table is created to maintain a historical data of that table.
Table with System Version is enabled to maintain past data history.
After expanding the original table we can view the historical table that was created.
Create a table with system version enabled:
CREATE TABLE dbo.Vembu
(
Product_id INT NOT NULL CONSTRAINT PK_Product PRIMARY KEY IDENTITY(1,1),
Category_id INT NULL,
Product_Name VARCHAR(255) NOT NULL,
STARTDATE DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL,
ENDDAATE DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (startdate, enddate)
)
WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.VembuHistory ) );
The above query will create a table with name vembu and history table with VembuHistory by using SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.VembuHistory statement .
When we are inserting a value into the System Version enabled table the inserted value will get inserted only into the main table(Vembu).
INSERT INTO dbo.Vembu (Category_id, Product_Name) VALUES
(1, ‘BDR’)
,(2, ‘SAAS’)
,(3, ‘Vembu360’));
For all records the enddate set to “9999-12-31 23:59:59”.
If we execute select query against Vembu table it will return above three rows. But when we execute a select query against the VembuHistory table it will return an empty set.
UPDATE dbo.Vembu SET Product_Name = ‘CRM’ WHERE Product_id = 3;
After executing update query the data in Vembu(original table) and VembuHistory(History) table will be like below.
Data in Vembu Table :
Data in VembuHistory Table :
After executing update query we can see that the data is inserted into historical vembu table with old data .
By using Temporal Table we can query our history tables if we need to know an exact value for a specific point in time.
Got questions? Email us at: vembu-support@vembu.com for answers.
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
Note: This is a Republished Article. Blog originally published on – March 17th, 2016.
