forum

Home / DeveloperSection / Forums / How temporal table enable historical data tracking and auditing in SQL Server?

Ask Question
Revati S Misra

Revati S Misra

Follow
Skilled in SEO, content writing, and digital marketing. Completed several years of working in many organizations including multinational companies. I love to learn new things in life that keep me motivated.

Can you answer this question?

Answer

1 Answers

Aryan Kumar

Aryan Kumar

19-Oct-2023

Enabling historical data tracking and auditing in SQL Server is made possible through the use of temporal tables. Temporal tables allow you to keep track of historical data changes in a structured and efficient manner. Here's how to enable them:

1. Create a Temporal Table:

To enable historical data tracking, you first need to create a temporal table. A temporal table consists of two parts: the current table and the history table.

-- Create the main (current) table
CREATE TABLE YourTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)

-- Create the history table
CREATE TABLE YourTable_History (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    ValidFrom DATETIME2 NOT NULL,
    ValidTo DATETIME2 NOT NULL
)

2. Enable System-Versioning:

To track historical data, you need to enable system-versioning on your temporal table.

-- Enable system-versioning on the main table
ALTER TABLE YourTable
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.YourTable_History))

3. Insert Data:

Now, as you insert, update, and delete records in the main table, SQL Server will automatically manage the historical data in the history table. It keeps track of when records were valid.

Insert a New Record:

INSERT INTO YourTable (ID, Name) VALUES (1, 'John')

Update Record:

UPDATE YourTable
SET Name = 'Jane'
WHERE ID = 1

Delete a Record:

DELETE FROM YourTable
WHERE ID = 1

4. Query Historical Data:

To retrieve historical data, you can use standard SQL queries. For example, to query the historical data for a specific record, use the FOR SYSTEM_TIME clause:

-- Query historical data for a specific record
SELECT *
FROM YourTable
FOR SYSTEM_TIME AS OF '2023-10-01 12:00:00'
WHERE ID = 1

This will return the state of the record as it existed at the specified point in time.

Temporal tables provide an excellent way to track historical data and enable auditing because they automatically manage historical versions of your data. This can be very useful for compliance, auditing, and historical analysis purposes.

Liked By