Are you familiar with Temporal table not temporary table? Well, if you are not, then you are in the right path.
Temporal Table
A temporal table is an user designed system-versioned table which keeps the full history of data changes of a table. This changes of data is fully managed by the SQL Server database engine. It was introduced in ANSI SQL 2011 and SQL Server first support it as built-in database feature from SQL Server 2016 version.
Why Temporal Table
Temporal table's has below use cases:
- Auditing and forensic analysis of data changes
- Reconstructing state of the data at any given time in the past
- Projecting trends of data changes
- Maintaining a slowly changing dimension for decision support applications
- Rebuilding data in case of accidental data changes or application errors
How it works
Temporal table works in pair. So, when we create a System-versioning temporal table two tables are created with identical schema.
Image-1: Temporal table and the associated history table (courtesy by Microsoft)
- The primary/current table which stores all current data
- The secondary/history table which preserve the history of changes
- Two additional datetime2 columns are required
- Period start column: Start time of the row
- Period end column: End time of the row. Default value is the max value i.e. 9999-12-31 23:59:59.9999999 to mark the column as active
Demo
Let's see the practical example. We will create a student table to store the data, make some updates and will examine the final output.
Create the table
IF OBJECT_ID('Students', 'U') IS NOT NULL
DROP TABLE Students;
GO
CREATE TABLE dbo.Students
(
[pk_id] int NOT NULL PRIMARY KEY CLUSTERED
, [sid] varchar(20) NOT NULL
, [name] varchar(50) NOT NULL
, [class] varchar(100) NOT NULL
, [address] nvarchar(1024) NOT NULL
, [startDate] datetime2 GENERATED ALWAYS AS ROW START
, [endDate] datetime2 GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME ([startDate], [endDate])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.StudentsHistory));
Image-2: Two tables are created
Populate dummy data
INSERT INTO Students ([pk_id],[sid],[name],[class],[address])
VALUES (1, 'S_001', 'Bob', 'Class-1', 'Dhaka, Bangladesh')
, (2, 'S_002', 'Smith', 'Class-1', 'Dhaka, Bangladesh')
, (3, 'S_003', 'John', 'Class-1', 'Dhaka, Bangladesh');
Now we will make some modification
UPDATE Students Set
[class] = 'Class-2'
WHERE [pk_id] = 1;
UPDATE Students Set
[class] = 'Class-3'
WHERE [pk_id] = 1;
Output
Display Students
table
SELECT * FROM Students;
Image-3: Output of Students table
SELECT * FROM StudentsHistory;
Image-4: Output of StudentsHistory table
Auditing the Students table.
SELECT * FROM Students
FOR SYSTEM_TIME
BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
ORDER BY [pk_id] ASC;
Image-5: Output of Students table audit
Important Notes
Few important notes of using Temporal table:
- Temporal table must have a primary key. However, the history table don't have a primary key
- The SYSTEM_TIME period columns must have a datatype of datetime2
- The Temporal or history table cannot be FILETABLE
- INSERT or UPDATE query directly on SYSTEM_TIME period columns is not allowed
- TRUNCATE TABLE is not allowed
- INSERT or UPDATE query directly on history table is not allowed
- TRIGGER has limited usage
Summary
Temporal table is very useful in case of audit and forensic analysis and preventing unwanted delete or update of data. System-versioning temporal table also shows track of data changes. In my experience, I used it in some projects where I need to preserve the history of data changes. Hopefully, it will be helpful to you also.