StarQuest Technical Documents

SQDR and SQL Server Temporal Tables

Last Update: 12 July 2017
Product: SQDR
Version: 4.50 and later
Article ID: SQV00DR038

Abstract

SQL Server 2016 introduced support for temporal tables, which provide information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time.

SQDR does not currently provide any explicit support for temporal tables, but it can be used to populate an existing temporal table by creating and altering the table prior to replication, and configuring the subscription to Use Existing Table.

Below is an example script for altering an existing table for use as a temporal table:

Solution:

Below is an example SQL script for altering an existing table for use as a temporal table:

-----------------------------------------------
-- 1. Temporal Tables must have a PK
-----------------------------------------------

ALTER TABLE dbo.TEST ADD CONSTRAINT
PK_TEST PRIMARY KEY CLUSTERED
(
AA
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-----------------------------------------------
-- 2. ROW START & ROW END Defined
-----------------------------------------------

ALTER TABLE dbo.TEST
ADD
BeginDate datetime2 GENERATED ALWAYS AS ROW START NOT NULL
DEFAULT SYSUTCDATETIME()
,
EndDate datetime2 GENERATED ALWAYS AS ROW END NOT NULL
DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
PERIOD FOR SYSTEM_TIME (BeginDate,EndDate);

-----------------------------------------------
-- 3. History table is added and linked
-----------------------------------------------

ALTER TABLE dbo.TEST
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TESTHistory));