System versioned tables automatically store the history of (changed) data and thus makes it possible to query earlier states of data.
A system versioned table needs a history table which stores the data (records) that had been changed are not valid anymore.
I try to demonstrate a basic usage of system versioned tables with the following simple SQL snippets.
Create a system versioned table
First, I need a table. A table can be system versioned if
It has a primary key
Two columns with datatype datetime2 (which are not explicitly set as nullable) with a generated alwasy as row start/end clause
A period clause that uses those two columns. The name of the period must be system_time.
A table that can be system versioned becomes system version if it is created with the with ( system_versioning = on … clause.
In the following create table statement, I specify the validFrom and validTo columns to be hidden because I don't want to see them when I query the table.
I also explicitly name the history table with the history_table = clause. Without it, SQL Server would name the history table something like MSSQL_TemporalHistoryFor_<table-obj-id>. (<table-obj-id> is the object id of the system versioned table).
--
-- Create a system-versioned table
--
create table dbo.timeTravel (
id integer identity primary key,
col_one integer,
col_two varchar(10),
-------------------------------------------------------------------------
--
-- Define period columns:
--
validFrom datetime2 generated always as row start hidden not null,
validTo datetime2 generated always as row end hidden not null,
period for system_time (validFrom, validTo)
)
with (
system_versioning = on (
history_table = dbo.timeTravel_log
)
);
I now change the data in the table. Before each change, I store the before-change-timestamp in a variable so that I can look back in time to that specific time later on.
It is important to use sysutcdatetime() rather than sysdatetime() because SQL server stores the UTC time in the history table
After changing the data, I can use the variables to query a specific state of the data with the for system_time as of clause:
The for system_time all clause selects all data: that of the system versioned table and that of the history table.
declare @tBeforeDeletion datetime2 = sysutcdatetime();
delete from timeTravel where id = 2;
declare @tBeforeUpdate datetime2 = sysutcdatetime();
update timeTravel set col_two = 'THREE' where col_one = 3;
declare @tBeforeInsert datetime2 = sysutcdatetime();
insert into timeTravel(col_one, col_two) values (2, 'TWO');
print('State of table before deletion:')
select * from dbo.timeTravel for system_time as of @tBeforeDeletion order by id;
print('State of table before update:')
select * from dbo.timeTravel for system_time as of @tBeforeUpdate order by id;
print('State of table before insert:')
select * from dbo.timeTravel for system_time as of @tBeforeInsert order by id;
print('Current state:');
select * from dbo.timeTravel order by id;
print('Total history:')
select * from dbo.timeTravel for system_time all order by id;
In order to drop a system versioned table, it first needs to be set to system_versioning = off (otherwise, the error Drop table operation failed on table … because it is not a supported operation on system-versioned temporal tables. is thrown).
Also, the history table needs to be dropped separately:
alter table dbo.timeTravel set (system_versioning = off);
drop table dbo.timeTravel;
drop table dbo.timeTravel_log;