Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

Home Forums How temporal table enable historical data tracking and auditing in SQL Server? – MindStick
Ask Question

1 Answers

Aryan Kumar
Aryan Kumar 19 Oct 2023
Report Abuse

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.

advertising
Markdown for AI

A clean, structured version of this page for AI assistants and LLMs.

Open .md