How to Create a Temporal Table for Tracking Historical Data

Creating a Temporal Table to Track Historical Data

Write a SQL query to create a temporal table for tracking historical data.

Solution:

-- Create a temporal table with system-versioning enabled.
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(50),
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Explanation:

• Purpose of the Query :
• The goal is to demonstrate how to create a temporal table to track historical changes to data.
• Key Components :
• SysStartTime and SysEndTime: Columns that track the validity period of each row.
• PERIOD FOR SYSTEM_TIME: Defines the time period for versioning.
• WITH (SYSTEM_VERSIONING = ON): Enables system-versioning and creates a history table.
• Why use Temporal Tables?:
• Temporal tables automatically maintain a history of changes, simplifying auditing and compliance.
• They provide a built-in mechanism for tracking data over time.
• Real-World Application :
• In HR systems, temporal tables track employee role changes or salary adjustments.

Additional Notes:

• Ensure that the database supports temporal tables (e.g., SQL Server 2016+).
• Regularly monitor storage usage for the history table.
• Important Considerations:
• Avoid enabling system-versioning on high-frequency transactional tables due to storage overhead.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a temporal table for tracking changes in a Products table, including columns for product ID, name, price, and validity period.
• Write a SQL query to enable system-versioning on an Orders table to track historical order statuses over time.
• Write a SQL query to create a temporal table for a Customers table, ensuring that changes to customer details are automatically recorded.
• Write a SQL query to add system-versioning to an existing Employees table, including a history table named EmployeeHistory.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Temporal Tables and Versioning Exercises Home.
Next SQL Exercise: Querying Current Data from a Temporal Table.