To produce a Temporal Table you first need a history table. This step can be skipped, and the history table automatically generated with a system generated name, but I prefer for my tables to be descriptively named.
The following script will generate my history table:
Create table History.Applicant
(ApplicantID int not null,
Forename varchar(50),
Surname varchar(50),
DateOfBirth date,
ValidFrom datetime2 NOT NULL,
ValidTo datetime2 NOT NULL
) with (Data_Compression = Page)
go
Next we need to build a table to hold our current data table:
Create table CRM.Applicant
(ApplicantID int identity(1,1) primary key,
Forename varchar(50),
Surname varchar(50),
DateOfBirth date,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL ,
ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL ,
PERIOD FOR SYSTEM_TIME (ValidFrom,ValidTo)
) with (Data_Compression = Page, SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.Applicant))
- a primary key
- a pair of fields, of type datetime2, to hold the valid from and to periods. These fields will be automatically populated for new and modified records
- a PERIOD FOR SYSTEM_TIME statement, identifying your period fields
- the SYSTEM_VERSIONING = ON statement, which enabled the versioning.
In SSMS's object explorer the history tables are not visible in their own right. Instead, they are displayed as a node under the main table.
insert CRM.Applicant (Forename, Surname, DateOfBirth)
values ('Jon','Doe','1972-08-12'),
('Jane','Doe','1976-05-23')
go
Waitfor Delay '00:05:00'
go
Update CRM.Applicant
Set DateOfBirth = '1977-05-23'
where ApplicantID = 2
go
Waitfor Delay '00:05:00'
go
Update CRM.Applicant
Set Surname = 'Dough'
where ApplicantID = 2
go
Waitfor Delay '00:05:00'
go
Update CRM.Applicant
Set Forename = 'Jayne'
where ApplicantID = 2
go
Select * from CRM.Applicant
Select * from History.Applicant
The following query will return data as it was at a particular point in time:
Select *
from CRM.Applicant
FOR SYSTEM_TIME AS OF '2018-05-28 17:55:00'
Select *
from CRM.Applicant
FOR SYSTEM_TIME BETWEEN '2018-05-28 17:57:00' AND '2018-05-28 18:05:00'
Select *
from CRM.Applicant
FOR SYSTEM_TIME CONTAINED IN ('2018-05-28 17:57:00', '2018-05-28 18:05:00')
Select *
from CRM.Applicant
FOR SYSTEM_TIME ALL
But how does this relate to GDPR?
Delete CRM.Applicant where ApplicantID = 1
go
Select * from CRM.Applicant
Select * from History.Applicant
go
Delete History.Applicant where ApplicantID = 1
go
Alter table CRM.Applicant
Set (SYSTEM_VERSIONING = OFF)
go
Delete History.Applicant where ApplicantID = 1
go
Alter table CRM.Applicant
Set (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.Applicant))
go
Select * from CRM.Applicant
Select * from History.Applicant
go
Happy SQLing!