Temporal Tables

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
Join Temporal Tables
Query the Temporal Tables
- Modifiers for History Table
Examples
Remove Temporal Tables

Temporal Table

Create Schema

Create a schema that will hold all of the history tables.

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

USE Sandbox;

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

Create Table

Create the first table that will have history, will load sample data from AdventureWorks database.

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;

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

-- 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));

-- 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.

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'
;

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

SELECT			AddressID
			  , AddressLine1
			  , City
			  , ProvinceCode
			  , ProvinceName
			  , CountryName
			  , PostalCode
			  , EffectiveFrom
			  , EffectiveTo
FROM			dbo.HomeAddress
;
GO

SELECT AddressID

, AddressLine1

, City

, ProvinceCode

, ProvinceName

, CountryName

, PostalCode

, EffectiveFrom

, EffectiveTo

FROM dbo.HomeAddress

;

Update the Address Table

When updating rows will be inserted into the history.HomeAddress table.

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

UPDATE dbo.HomeAddress

SET AddressLine1 = '456 Iron Way'

WHERE AddressID = (SELECT MAX(AddressID) FROM dbo.HomeAddress)

;

UPDATE dbo.HomeAddress

SET AddressLine1 = '3333 Bouncy Street'

WHERE AddressID = (SELECT MAX(AddressID) FROM dbo.HomeAddress)

;

View Updated Data

Select query to show the updated data. The EffectiveTo date has a value of 9999-12-31

SELECT			AddressID
			  , AddressLine1
			  , City
			  , ProvinceCode
			  , ProvinceName
			  , CountryName
			  , PostalCode
			  , EffectiveFrom
			  , EffectiveTo
FROM			dbo.HomeAddress
;

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

SELECT			AddressID
			  , AddressLine1
			  , City
			  , ProvinceCode
			  , ProvinceName
			  , CountryName
			  , PostalCode
			  , EffectiveFrom
			  , EffectiveTo
FROM			history.HomeAddress
;

SELECT AddressID

, AddressLine1

, City

, ProvinceCode

, ProvinceName

, CountryName

, PostalCode

, EffectiveFrom

, EffectiveTo

FROM history.HomeAddress

;

Select Values as of Specified Date

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
;

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

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;

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

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

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)

)

Update the Record

Update the record and create some records in the history.Company table

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

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

;

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

;

View Current and Historical Records

SELECT		*
FROM		history.Company
;
SELECT		*
FROM		dbo.Company
;
SELECT		*
FROM		history.HomeAddress
;
SELECT		*
FROM		dbo.HomeAddress
;

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.

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));

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

SELECT		*
FROM		history.Company
;
SELECT		*
FROM		dbo.Company
;

SELECT *

FROM history.Company

;

SELECT *

FROM dbo.Company

;

Modify the Home Address History Table

Same process as modify the company table.

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));

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

SELECT		*
FROM		history.HomeAddress;
SELECT		*
FROM		dbo.HomeAddress
;

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

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

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

;

Example 2

Expected Result

— 2022-08-01
— Bucket Store / Eeyore D. / Station E

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

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

;

Example 3

Expected Result

— 2022-08-15
— Bucket Store / Eeyore D. / 456 Iron Way

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

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

;

Remove Temporal Tables

Turn System Versioning off, which will then allow you to drop the tables.

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;

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;

Trying to share some code ideas

Temporal Table

Create Schema

Create Table

Alter Existing Table

Populate Table

View Initial Data

Update the Address Table

View Updated Data

View Historical Data

Select Values as of Specified Date

Join Temporal Tables

Create a Second Temporal Table

Populate with Initial Values

Update the Record

View Current and Historical Records

Modify Company History Records

View the Changes

Modify the Home Address History Table

View the Changes

Query the Temporal Tables

Modifiers for History Table

Examples

Example 1

Example 2

Example 3

Remove Temporal Tables

Leave a Reply Cancel reply

Temporal Tables

Temporal Table

Create Schema

Create Table

Alter Existing Table

Populate Table

View Initial Data

Update the Address Table

View Updated Data

View Historical Data

Select Values as of Specified Date

Join Temporal Tables

Create a Second Temporal Table

Populate with Initial Values

Update the Record

View Current and Historical Records

Modify Company History Records

View the Changes

Modify the Home Address History Table

View the Changes

Query the Temporal Tables

Modifiers for History Table

Examples

Example 1

Example 2

Example 3

Remove Temporal Tables

Foreign Key List

Leave a Reply Cancel reply