This post is part of the serie 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the serie!
- What's new in SQL Server 2016: Json
- Comparing execution plans with SQL Server 2016
- What's new in SQL Server 2016: Dynamic Data Masking
- What's new in SQL Server 2016: Row-Level Security
- What's new in SQL Server 2016: Stretch Database
- What's new in SQL Server 2016: Live Query Statistics
- What's new in SQL Server 2016: Temporal Tables (this post)
- What's new in SQL Server 2016: Always encrypted
- SQL Server 2016 SP1 - Licensing changes
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 an history of the data of a table. A Temporal Table is actually two linked tables. The first contains the current data while the second contains the historical data. All
DELETE are executed on the first table and SQL Server automatically copy the old data to the history table. The interest is then to be able to request these historical data simply.
For the example we will create a table containing currency exchange rates:
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
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
DELETE queries on the
ExchangeRate table. If you try to directly modify the
ExchangeRateHistory table you will get an error message.
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 the 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:
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.
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:
SELECT * FROM MyView FOR SYSTEM_TIME BETWEEN '2015-01-01' AND '2015-01-01' WHERE Currency = 'EUR'
Temporal Tables allow you to add an 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!