Terms
Inserts – Updates – Deletes - Merge
Overview
Temporal tables (also known as system-versioned temporal tables) are a database feature that brings built-in support for providing 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
Temporal tables are an advanced upgradation to the concept of "cdc : change data capture in older versions"
A mechanism to create additional history tracking tables based on row updates & deletes on the base table.
Whenever we perform any update / delete to the base table - Such operations are auto auditted to additional [auto created] "temporal table"
This temporal table is used for tracking of historical data by dbas
This temporal table is used for incremental data loads (etl in dwh db design) by bi developers.
This temporal table is used for faster data recovery incase of accidental updates & deletes.
Temporal tables are new feature from sql server 2017.
Usage
Create Table
CREATE TABLE dbo.Employee (
[EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
[Name] NVARCHAR(100) NOT NULL,
[Position] VARCHAR(100) NOT NULL,
[Department] VARCHAR(100) NOT NULL,
[Address] NVARCHAR(1024) NOT NULL,
[AnnualSalary] DECIMAL(10, 2) 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));
Select Statement
SELECT * FROM Employee
FOR SYSTEM_TIME
BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000 ORDER BY ValidFrom;
History Table
UPDATE EmployeeHistory SET EMP_NAME = ' ' -- CANNOT UPDATE HISTORY TABLE
DELETE FROM EmployeeHistory -- CANNOT DELETE HISTORY TABLE
DROP TABLE EmployeeHistory -- CANNOT DROP HISTORY TABLE
