Where we are going, we need to keep track of the history in the tables.
How to set up and use Temporal Tables in Microsoft SQL Microsoft SQL has native support for keeping track of Insert / Update / Delete from system versioned tables. The following explains how to set up and use temporal tables in Microsoft SQL.
Temporal Table
Create Schema
Create a schema that will hold all of the history tables.
1 2 3 4 5 6 7 8 |
USE Sandbox; GO IF NOT EXISTS (SELECT [name] FROM Sandbox.sys.schemas WHERE [name] = 'history') BEGIN DECLARE @sql NVarchar(64) = 'CREATE SCHEMA history AUTHORIZATION [DBO];' EXECUTE sp_executesql @sql; END GO |
Create Table
Create the first table that will have history, will load sample data from AdventureWorks database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
IF EXISTS ( SELECT [TABLE_NAME] FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND [TABLE_NAME] = 'HomeAddress' AND [TABLE_SCHEMA] = 'dbo' ) BEGIN ALTER TABLE dbo.HomeAddress SET (SYSTEM_VERSIONING = OFF); TRUNCATE TABLE dbo.HomeAddress; ALTER TABLE dbo.HomeAddress SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.HomeAddress)); END; ELSE BEGIN CREATE TABLE dbo.HomeAddress ( AddressID Int NOT NULL PRIMARY KEY CLUSTERED , AddressLine1 NVarchar(128) NULL , City NVarchar(32) NULL , ProvinceCode NVarchar(8) NULL , ProvinceName NVarchar(64) NULL , CountryName NVarchar(64) NULL , PostalCode NVarchar(16) NULL -- can add HIDDEN flag when creating columns , EffectiveFrom DateTime2 GENERATED ALWAYS AS ROW START NOT NULL , EffectiveTo DateTime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME (EffectiveFrom, EffectiveTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.HomeAddress)); ; END; |
Alter Existing Table
Need to alter the existing table
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Add time stamp columns -- by default make the columns hidden ALTER TABLE temp.booking ADD EffectiveFrom DateTime2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_Booking_EffectiveFrom DEFAULT SYSUTCDATETIME() , EffectiveTo DateTime2 GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_Booking_EffectiveTo DEFAULT CONVERT(DateTime2, '9999-12-31 23:59:59.9999999') , PERIOD FOR SYSTEM_TIME (EffectiveFrom, EffectiveTo) -- Add the primary key ALTER TABLE temp.booking ADD CONSTRAINT PK_Booking PRIMARY KEY (booking_id); -- Turn on system versioning ALTER TABLE temp.booking SET (SYSTEM_VERSIONING=ON (HISTORY_TABLE=history.booking)); |
Populate Table
Data comes from AdventureWorks database. Feel free to substitute your own.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
INSERT INTO DBO.HomeAddress ( AddressID , AddressLine1 , City , ProvinceCode , ProvinceName , CountryName , PostalCode ) SELECT TOP (5) addr.AddressID , addr.AddressLine1 , addr.City , StateProvince.StateProvinceCode ProvinceCode , StateProvince.Name ProvinceName , Country.Name CountryName , addr.PostalCode FROM AdventureWorks.person.Address addr INNER JOIN AdventureWorks.Person.StateProvince StateProvince ON StateProvince.StateProvinceID = addr.StateProvinceID INNER JOIN AdventureWorks.Person.CountryRegion Country ON Country.CountryRegionCode = StateProvince.CountryRegionCode WHERE Country.Name = 'Canada' AND StateProvince.Name = 'Ontario' ; |
View Initial Data
Make sure the data was added correctly
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT AddressID , AddressLine1 , City , ProvinceCode , ProvinceName , CountryName , PostalCode , EffectiveFrom , EffectiveTo FROM dbo.HomeAddress ; GO |
Update the Address Table
When updating rows will be inserted into the history.HomeAddress table.
1 2 3 4 5 6 7 8 9 10 |
UPDATE dbo.HomeAddress SET AddressLine1 = '456 Iron Way' WHERE AddressID = (SELECT MAX(AddressID) FROM dbo.HomeAddress) ; GO UPDATE dbo.HomeAddress SET AddressLine1 = '3333 Bouncy Street' WHERE AddressID = (SELECT MAX(AddressID) FROM dbo.HomeAddress) ; GO |
View Updated Data
Select query to show the updated data. The EffectiveTo date has a value of 9999-12-31
1 2 3 4 5 6 7 8 9 10 11 |
SELECT AddressID , AddressLine1 , City , ProvinceCode , ProvinceName , CountryName , PostalCode , EffectiveFrom , EffectiveTo FROM dbo.HomeAddress ; |
View Historical Data
Now show previous values. You can see the date ranges applied to EffectiveFrom and EffectiveTo
1 2 3 4 5 6 7 8 9 10 11 |
SELECT AddressID , AddressLine1 , City , ProvinceCode , ProvinceName , CountryName , PostalCode , EffectiveFrom , EffectiveTo FROM history.HomeAddress ; |
Select Values as of Specified Date
1 2 3 4 5 6 7 8 9 |
DECLARE @get_EffectiveFrom AS DateTime2 SELECT @get_EffectiveFrom = MIN(EffectiveFrom) FROM history.HomeAddress HomeAddress WHERE HomeAddress.AddressID = (SELECT MAX(AddressID) FROM dbo.HomeAddress) SELECT @get_EffectiveFrom get_EffectiveFrom SELECT * FROM dbo.HomeAddress FOR SYSTEM_TIME AS OF @get_EffectiveFrom ; |
Join Temporal Tables
Create a Second Temporal Table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
IF EXISTS ( SELECT [TABLE_NAME] FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND [TABLE_NAME] = 'Company' AND [TABLE_SCHEMA] = 'dbo' ) BEGIN ALTER TABLE dbo.Company SET (SYSTEM_VERSIONING = OFF); TRUNCATE TABLE dbo.Company; ALTER TABLE dbo.Company SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.CompanyName)); END; ELSE BEGIN CREATE TABLE dbo.Company ( CompanyId Int NOT NULL PRIMARY KEY CLUSTERED , AddressID Int NOT NULL -- FK , CompanyName NVarchar(32) NULL , CompanyOwner NVarchar(32) NULL -- can add HIDDEN flag when creating columns , EffectiveFrom DateTime2 GENERATED ALWAYS AS ROW START NOT NULL , EffectiveTo DateTime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME(EffectiveFrom, EffectiveTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.Company)); ; END; |
Populate with Initial Values
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE @get_AddressId AS Int SELECT @get_AddressId = MAX(HomeAddress.AddressID) FROM dbo.HomeAddress HomeAddress SELECT @get_AddressId get_AddressId --DECLARE @get_AddressId AS Int = 31; INSERT INTO dbo.Company ( CompanyId , AddressID , CompanyName , CompanyOwner ) VALUES (100 -- CompanyId - int , @get_AddressId -- AddressID - int , 'HoneyStore' -- CompanyName - nvarchar(32) , 'Winnie T.' -- CompanyOwner - nvarchar(32) ) GO |
Update the Record
Update the record and create some records in the history.Company table
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
UPDATE dbo.Company SET AddressID = (SELECT MAX(HomeAddress.AddressID) FROM dbo.HomeAddress) , CompanyName = 'Bucket Store' -- CompanyName - nvarchar(32) , CompanyOwner = 'Eeyore D.' -- CompanyOwner - nvarchar(32) WHERE CompanyId = 100 ; GO UPDATE dbo.Company SET AddressID = (SELECT MAX(HomeAddress.AddressID) FROM dbo.HomeAddress) , CompanyName = 'Springs Springs' -- CompanyName - nvarchar(32) , CompanyOwner = 'Tigger T.' -- CompanyOwner - nvarchar(32) WHERE CompanyId = 100 ; go |
View Current and Historical Records
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT * FROM history.Company ; SELECT * FROM dbo.Company ; SELECT * FROM history.HomeAddress ; SELECT * FROM dbo.HomeAddress ; |
Modify Company History Records
Will need to turn off system versioning and after updating turn the system versioning back on.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
ALTER TABLE dbo.Company SET (SYSTEM_VERSIONING = OFF); SELECT * FROM history.Company UPDATE history.Company SET EffectiveFrom = '2022-07-01' ,EffectiveTo = '2022-08-01' WHERE CompanyName = 'HoneyStore' ; DECLARE @get_EffectiveTo AS DateTime2 SELECT @get_EffectiveTo = Company.EffectiveFrom FROM dbo.Company Company WHERE Company.CompanyId = 100 UPDATE history.Company SET EffectiveFrom = '2022-08-01' ,EffectiveTo = @get_EffectiveTo WHERE CompanyName = 'Bucket Store' ; ALTER TABLE dbo.Company SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.Company)); |
View the Changes
1 2 3 4 5 6 |
SELECT * FROM history.Company ; SELECT * FROM dbo.Company ; |
Modify the Home Address History Table
Same process as modify the company table.
1 2 3 4 5 6 7 8 9 10 11 |
ALTER TABLE dbo.HomeAddress SET (SYSTEM_VERSIONING = OFF); UPDATE history.HomeAddress SET EffectiveFrom = '2022-07-01' , EffectiveTo = '2022-08-15' WHERE AddressLine1 = 'Station E' ; UPDATE history.HomeAddress SET EffectiveFrom = '2022-08-15' WHERE AddressLine1 = '456 Iron Way' ; ALTER TABLE dbo.HomeAddress SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.HomeAddress)); |
View the Changes
1 2 3 4 5 |
SELECT * FROM history.HomeAddress; SELECT * FROM dbo.HomeAddress ; |
Query the Temporal Tables
Modifiers for History Table
Expression | Qualifying Rows | Note |
---|---|---|
AS OF date_time | ValidFrom <= date_time AND ValidTo > date_time | Returns a table with rows containing the values that were current at the specified point in time in the past. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the date_time parameter. The value for a row is deemed valid if the system_start_time_column_name value is less than or equal to the date_time parameter value and the system_end_time_column_name value is greater than the date_time parameter value. |
FROM start_date_time TO end_date_time | ValidFrom < end_date_time AND ValidTo > start_date_time | Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the start_date_time parameter value for the FROM argument or ceased being active after the end_date_time parameter value for the TO argument. Internally, a union is performed between the temporal table and its history table. The results are filtered to return the values for all row versions that were active at any time during the time range specified. Rows that stopped being active exactly on the lower boundary defined by the FROM endpoint aren’t included, and records that became active exactly on the upper boundary defined by the TO endpoint are also not included. |
BETWEEN start_date_time AND end_date_time | ValidFrom <= end_date_time AND ValidTo > start_date_time | Same as previous in the FOR SYSTEM_TIME FROM start_date_time TO end_date_time description, except the table of rows returned includes rows that became active on the upper boundary defined by the end_date_time endpoint. |
CONTAINED IN (start_date_time, end_date_time) | ValidFrom >= start_date_time AND ValidTo <= end_date_time | Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two period values for the CONTAINED IN argument. Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included. |
ALL | All rows | Returns the union of rows that belong to the current and the history table. |
Expression | Example |
---|---|
AS OF date_time | SELECT * FROM dbo.Table_Name FOR SYSTEM_TIME AS OF ‘2023-01-01’ |
FROM start_date_time TO end_date_time | SELECT * FROM dbo.Table_Name FOR SYSTEM_TIME FROM ‘2023-01-01’ TO ‘2023-12-31’ |
BETWEEN start_date_time AND end_date_time | SELECT * FROM dbo.Table_Name FOR SYSTEM_TIME BETWEEN ‘2023-01-01’ AND ‘2023-12-31’ |
CONTAINED IN (start_date_time, end_date_time) | SELECT * FROM dbo.Table_Name FOR SYSTEM_TIME CONTAINED IN (‘2023-01-01’, ‘2023-12-31’) |
ALL | SELECT * FROM dbo.Table_Name FOR SYSTEM_TIME ALL |
Examples
Example 1
Expected Result
— 2022-07-15
— HoneyStore / Winnie T. / Station E
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @dt_1 AS DateTime2 = '2022-07-15'; SELECT Company.CompanyId , Company.AddressID , Company.CompanyName , Company.CompanyOwner , HomeAddress.AddressLine1 FROM dbo.Company FOR SYSTEM_TIME AS OF @dt_1 company INNER JOIN dbo.HomeAddress FOR SYSTEM_TIME AS OF @dt_1 HomeAddress ON Company.AddressID = HomeAddress.AddressID ; go |
Example 2
Expected Result
— 2022-08-01
— Bucket Store / Eeyore D. / Station E
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @dt_1 AS DateTime2 = '2022-08-01'; SELECT Company.CompanyId , Company.AddressID , Company.CompanyName , Company.CompanyOwner , HomeAddress.AddressLine1 FROM dbo.Company FOR SYSTEM_TIME AS OF @dt_1 company INNER JOIN dbo.HomeAddress FOR SYSTEM_TIME AS OF @dt_1 HomeAddress ON Company.AddressID = HomeAddress.AddressID ; go |
Example 3
Expected Result
— 2022-08-15
— Bucket Store / Eeyore D. / 456 Iron Way
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @dt_1 AS DateTime2 = ' 2022-08-15'; SELECT Company.CompanyId , Company.AddressID , Company.CompanyName , Company.CompanyOwner , HomeAddress.AddressLine1 FROM dbo.Company FOR SYSTEM_TIME AS OF @dt_1 company INNER JOIN dbo.HomeAddress FOR SYSTEM_TIME AS OF @dt_1 HomeAddress ON Company.AddressID = HomeAddress.AddressID ; go |
Remove Temporal Tables
Turn System Versioning off, which will then allow you to drop the tables.
1 2 3 4 5 6 7 8 |
ALTER TABLE dbo.Company SET (SYSTEM_VERSIONING = OFF); DROP TABLE dbo.Company; DROP TABLE history.Company; ALTER TABLE dbo.HomeAddress SET (SYSTEM_VERSIONING = OFF); DROP TABLE dbo.HomeAddress; DROP TABLE history.HomeAddress; |
Leave a Reply