I recently did a little overview at work on SQL 2016. One of the items on show was Temporal Tables; one of my favourite feature of SQL 2016.
Temporal tables are basically system versioning tables. You can enable System Versioning on an existing or new table, and a history table will be created for it. Then, any changes you make to the data in the table, such as updates and deletes, will be audited in the history table.
For example, if we create a table with system versioning enabled:
CREATE TABLE dbo.TestTemporalFootballers (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY ,Name varchar(100) ,Age int ,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL ,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL ,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.FootballersHistory)); Which will look as follows in SQL:
One thing to note here are the two datetime2 fields; these must be specified on the table you are setting System Versioning On for. Another thing to note is the name of the history table, which we have given as “FootballersHistory”; if we didn’t supply a name, it would create a default name for this table in the format of MSSQL_TemporalHistoryFor__[suffix] (suffix will only be added if the first part of the table name is not unique).
If we insert some test data:
INSERT INTO TestTemporalFootballers (Name, Age) VALUES ('Cristiano', 34) INSERT INTO TestTemporalFootballers (Name, Age) VALUES ('Alexis', 27) INSERT INTO TestTemporalFootballers (Name, Age) VALUES ('Mbappe', 18) And then view our data:
SELECT * FROM dbo.TestTemporalFootballers SELECT * FROM dbo.FootballersHistory 
The new records have been added into the TestTemporalFootballers table, but the history table (FootballersHistory) is empty, as there have been no changes to the data in the TestTemporalFootballers table.
Also note the SysStartTime and SysEndTime. SysStartTime will record the time the data was either added or updated; the DateTime the data became valid. As for SysEndTime, this will always be Max DateTime, and is a pretty much useless field.
If we make the following changes:
UPDATE TestTemporalFootballers SET Age = 20 WHERE ID = 1 DELETE FROM TestTemporalFootballers WHERE ID = 2 And we view our tables now:
SELECT * FROM dbo.TestTemporalFootballers
SELECT * FROM dbo.FootballersHistory
We can see that 2 new records have been added; the first being when we updated the age from 34 to 20, and the second being the record that was deleted. SysStartTime and SysEndTime fields specify the DateTime the data was valid, i.e. live, from and to.
Moving on, there are restrictions on the changes you can make to a system versioned controlled table, in fact, you are only allowed the following commands on a system versioned controlled table: rebuild, create index and create statistics.
For example, if you try to drop our TestTemporalFootballers table, you will receive the error message:
Drop table operation failed on table 'SQLBrownBag.dbo.TestTemporalFootballers' because it is not supported operation on system-versioned temporal tables.
To drop the table (amongst other schema changes) you will need to turn off system versioning using the following statement:
ALTER TABLE dbo.TestTemporalFootballers SET (SYSTEM_VERSIONING = OFF);At which point, the history table will become a normal table, and you may make any you like to the tables.
Another important point to note is that you cannot truncate the history table, and they must live on the same database as the table which is being versioned. An important factor if you have large tables where disk space could be an issue.
In conclusion, this is a great new feature which could make column level corruptions much easier to rectify. Although you could have had the same functionality in previous versions of SQL using triggers, although this make it much easier.
Comments