In Back to the Future, Marty McFly travels through time in a mad yet loveable scientist's DeLorean. While the movie is a work of fiction, the concept of using a time machine isn't too far off from what we can do with SQL Server temporal tables.
Temporal tables keep track of changes to data over time, allowing you to view current data at any point in the past and helping you figure out what changed. It's all done via two datetime2 data type columns:
ValidFrom
The database sets the current time (UTC) when adding a new row.
ValidTo
The database sets the value to 9999-12-31 when adding a new row, indicating it's the current value. This gets replaced with the ValidFrom date when an existing row is updated.
No Plutonium Required
Rows? Where we're going, we don't need rows!
Let's punch it to 88 and create an employee table using this feature in SQL:
CREATE TABLE dbo.Employee
(
[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
, [Firstname] nvarchar(100) NOT NULL
, [Lastname] varchar(100) NOT NULL
, [StartDate] [date] NOT NULL,
, [Department] varchar(100) NOT NULL
, [AnnualSalary] decimal (10,2) NOT NULL
, [UpdatedBy] varchar(100) NOT NULL
, [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
, [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
💡 I highly recommend using an UpdatedBy column in your table, pipelines, and applications. It can save you and your teams hours trying to solve a wild Whodunit. UpdatedBy will allow you to see exactly who or what updated data.
The last line in our create table tells SQL to create a temporal table named "EmployeeHistory."
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
Now SQL will automatically create a historical record for all inserts, updates, deletes, and merges into EmployeeHistory, allowing you to query using the clause "FOR System_Time."
SELECT * FROM EmployeeHhistory
FOR SYSTEM_TIME
BETWEEN '1985-01-01 00:00:00.0000000' AND '1985-12-31 00:00:00.0000000'
WHERE EmployeeID = 1 ORDER BY ValidFrom;
Valid Expressions are AS OF [datetime], FROM [datetime] TO [datetime], BETWEEN [datetime] AND [datetime], and CONTAINED IN (start_datetime, end_datetime)
Just as Marty McFly had to be careful not to change the past and create a new future, we need to be careful when using temporal tables. In the movie, Marty made a small change that could have resulted in his parents never meeting and him never being born. With SQL Server temporal tables, we can unintentionally make changes that have a ripple effect on other data. For example, suppose we change accrued vacation days on an employee's record; that's not a good outcome for anyone.
When used correctly, temporal tables can be a powerful tool for managing data changes. They help you track down when and how data changes, and they can help you ensure our data is correct. Like the DeLorean time machine, temporal tables can help us take a trip back to the future.