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.
Go to:
PREV : Temporal Tables and Versioning Exercises Home.
NEXT : Querying Current Data from a Temporal Table.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.