This post is part of the series 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the series!
The final version of SQL Server 2016 has been released recently. So, it's time to take a look at the new features provided by this version. Today we will discover the Temporal Tables.
Temporal Tables allow you to automatically keep a history of the data of a table. A Temporal Table is two linked tables. The first contains the current data while the second contains the historical data. All INSERT, UPDATE, DELETE are executed on the first table and SQL Server automatically copies the old data to the history table. The interest is then to be able to request these historical data simply.
For example, we will create a table containing currency exchange rates:
SQL
CREATE TABLE dbo.ExchangeRate
(
Id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
Currency nchar(3) NOT NULL,
Rate float NOT NULL,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL, -- add HIDDEN for hiding this column in SELECT *
ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (
ValidFrom,
ValidTo
)
)
WITH (SYSTEM_VERSIONING = ON
(
HISTORY_TABLE = dbo.ExchangeRateHistory,
DATA_CONSISTENCY_CHECK = ON, -- Ensure ValidFrom <= ValidTo
))
GO
To create the table we declare the columns we need then we add 2 technical columns ValidFrom and ValidTo that we declare as PERIOD FOR SYSTEM_TIME. SQL Server knows the columns to use when you need to access the history. It also specifies the name of the second ExchangeRateHistory table that will be used to back up the data history.
To add or modify data, just use the classic INSERT, UPDATE, DELETE queries on the ExchangeRate table. If you try to directly modify the ExchangeRateHistory table you will get an error message.
SQL
INSERT INTO ExchangeRate(Currency, Rate)
VALUES('USD', 1)
INSERT INTO ExchangeRate(Currency, Rate)
VALUES('EUR', 1)
UPDATE ExchangeRate
SET Rate = 2
WHERE Currency = 'EUR'
UPDATE ExchangeRate
SET Rate = 1.5
WHERE Currency = 'EUR'
UPDATE ExchangeRate
SET Rate = 1.3
WHERE Currency = 'EUR'
UPDATE ExchangeRate
SET Rate = 1.4
WHERE Currency = 'EUR'
The table contains only 2 rows:
And the history table contains a little more rows:
For example, it is interesting to modify the dates of the history table. To do this we deactivate the link between the 2 tables before modifying the data, then we reactivate it:
SQL
ALTER TABLE ExchangeRate SET ( SYSTEM_VERSIONING = OFF )
GO
UPDATE ExchangeRateHistory SET ValidFrom='2015-01-01 00:00:00', ValidTo='2015-01-01 23:59:59' WHERE Rate = 1
UPDATE ExchangeRateHistory SET ValidFrom='2015-01-02 00:00:00', ValidTo='2015-01-02 23:59:59' WHERE Rate = 2
UPDATE ExchangeRateHistory SET ValidFrom='2015-01-03 00:00:00', ValidTo='2015-01-03 23:59:59' WHERE Rate = 1.5
UPDATE ExchangeRateHistory SET ValidFrom='2015-01-04 00:00:00', ValidTo='2015-01-04 23:59:59' WHERE Rate = 1.3
UPDATE ExchangeRateHistory SET ValidFrom='2015-01-05 00:00:00', ValidTo='2015-01-05 23:59:59' WHERE Rate = 1.4
ALTER TABLE ExchangeRate SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ExchangeRateHistory))
GO
Instead of requesting the history table, you can use the operator FOR SYSTEM_TIME when requesting the main table:
FOR SYSTEM_TIME AS OF '2015-01-01'FOR SYSTEM_TIME FROM '2015-01-01' TO '2015-01-05'FOR SYSTEM_TIME BETWEEN '2015-01-01' AND '2015-01-05'FOR SYSTEM_TIME CONTAINED IN ('2015-01-01', '2015-01-05')
FOR SYSTEM_TIME can be used for a table or a view. In the case of a view, the filter is applied for all tables referenced by the view.
SQL
SELECT * FROM ExchangeRate
FOR SYSTEM_TIME AS OF '2015-01-02'
WHERE Currency = 'EUR'
SELECT * FROM ExchangeRate
FOR SYSTEM_TIME BETWEEN '2015-01-01' AND '2015-01-02'
WHERE Currency = 'EUR'
For a view the syntax is the same:
SQL
SELECT * FROM MyView
FOR SYSTEM_TIME BETWEEN '2015-01-01' AND '2015-01-01'
WHERE Currency = 'EUR'
#Conclusion
Temporal Tables allow you to add a history feature to the database in a seamless way. Indeed, following the activation of this one, no modification of the requests or the applications is necessary. To query on the history the syntax is simple FOR SYSTEM_TIME .... In short very practical!
Do you have a question or a suggestion about this post? Contact me!
