Temporal
Tables:
Temporal
tables are nothing but maintaining historical information for the rows
by the SQL server itself for the given table.
Instead
of implementing SCD for the tables by the developers, if you create
the table as Temporal Table then SQL Server automatically will take
care of maitaining the history for the changing data.
To
Create the temporal tables we should use below syntax (Here i have created Department table):
CREATE
TABLE
dbo.Department
(
DepartmentID
int NOT
NULL
IDENTITY(1,1)
PRIMARY
KEY
CLUSTERED,
DepartmentName
varchar(50)
NOT
NULL,
ManagerID
int
NULL,
ValidFrom
datetime2
GENERATED
ALWAYS
AS
ROW
START
NOT
NULL
DEFAULT
CAST('1900-01-01'
AS
DATETIME2)
,
ValidTo
datetime2
GENERATED
ALWAYS
AS
ROW
END
NOT
NULL
DEFAULT
CAST('9999-12-31'
AS
DATETIME2)
,
PERIOD
FOR
SYSTEM_TIME
(
StartDate,
EndDate
)
)
WITH
(
SYSTEM_VERSIONING
=
ON
(HISTORY_TABLE
= dbo.DepartmentHistory)
);
GO |
1.
Two additional Start & End date Audit columns of datetime2
datatype for capturing the validity of records. You can use any
meaningful column name here, we will use StartDate
and EndDate
column names
2.
Both the column names have to be specified in PERIOD
FOR SYSTEM_TIME (StartDate, EndDate) clause
along with the columns.
3. Specify WITH
(SYSTEM_VERSIONING = ON)
option at the end of the CREATE TABLE statement with optional
(HISTORY_TABLE
= <<History_Table_Name>>)
option.
Once
you create the table it will automatically create two tables
internally
one
for current data and other for the historical data.
When
you are querying the table, you can mention the table name that you
have mentioned in Create table statement that will automatically call
historical table internally and will return the results.
If
we want to see only historical records then we can query the
historical table that we have mentioned in our create table script.
If
there are no updates to the data then no rows will be available in
the historical table.
Limitation
of Temporal Tables:
1.
We can't query Temporal tables using Linked
Server
2.
History table should not have constraints
(PK, FK, Table or Column constraints).
3.
INSERT
and UPDATE
statements cannot reference the SYSTEM_TIME period columns.
4.
TRUNCATE
TABLE
is not supported till SYSTEM_VERSIONING is ON and we can truncate if
we Switch OFF this setting
5.
Direct modification
of the data in a history table is not permitted.
6.
INSTEAD
OF triggers
are not permitted on either the tables.
7.
Usage of Replication
technologies is limited.
For more information refer Temporal tables Part 1 and Part 2
