Validity Periods in Temporal Tables
Temporal tables in SQL enable you to track historical data changes by associating rows with specific validity periods. Validity periods specify the time intervals during which a row is valid, allowing you to maintain a history of changes in your data.
Introduction to Validity Periods in Temporal Tables
What Are Validity Periods?
Validity periods represent time intervals during which a row in a temporal table is valid. These periods allow SQL to maintain a history of changes and provide insight into how data evolves over time.
Importance of Validity Periods
- Data Auditing: Track who made changes and when.
- Historical Analysis: Retrieve data as it existed at a specific time.
- Legal Compliance: Maintain a record of data changes for regulatory requirements.
Types of Validity Periods
System-Time Validity
System-time validity automatically tracks changes in rows with timestamps managed by the database system.
Example:
- Start Time: When the row became valid.
- End Time: When the row ceased to be valid.
Application-Time Validity
Application-time validity defines validity periods explicitly based on business rules or application requirements.
Example:
- Start Date: Start of a product promotion.
- End Date: End of the promotion.
Bi-Temporal Validity
Combines system-time and application-time periods for comprehensive temporal data management.
Creating Temporal Tables with Validity Periods
Syntax and Structure
Here’s how to create a temporal table with system-time validity:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(100),
Position NVARCHAR(50),
Salary DECIMAL(10, 2),
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON);
Explanation:
ValidFromandValidTo: Track the validity period.PERIOD FOR SYSTEM_TIME: Marks the columns as system-time period.SYSTEM_VERSIONING = ON: Enables automatic versioning.
Example: Adding Data
INSERT INTO Employees (EmployeeID, Name, Position, Salary)
VALUES (1, 'Alice', 'Developer', 75000);
Example: Updating Data
UPDATE Employees
SET Salary = 80000
WHERE EmployeeID = 1;
Output (Automatically Managed):
| EmployeeID | Name | Position | Salary | ValidFrom | ValidTo |
|---|---|---|---|---|---|
| 1 | Alice | Developer | 75000 | 2024-01-01 00:00:00 | 2024-06-01 00:00:00 |
| 1 | Alice | Developer | 80000 | 2024-06-01 00:00:00 | 9999-12-31 23:59:59 |
Querying Data with Validity Periods
Retrieving Current Data
SELECT * FROM Employees FOR SYSTEM_TIME AS OF '2024-06-01';
Output:
| EmployeeID | Name | Position | Salary | ValidFrom | ValidTo |
|---|---|---|---|---|---|
| 1 | Alice | Developer | 80000 | 2024-06-01 00:00:00 | 9999-12-31 23:59:59 |
Retrieving Historical Data
SELECT * FROM Employees FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-06-01';
Output:
| EmployeeID | Name | Position | Salary | ValidFrom | ValidTo |
|---|---|---|---|---|---|
| 1 | Alice | Developer | 75000 | 2024-01-01 00:00:00 | 2024-06-01 00:00:00 |
Advanced Concepts
Bi-Temporal Queries
SELECT * FROM Employees
WHERE ValidFrom BETWEEN '2024-01-01' AND '2024-12-31'
AND ApplicationStartDate BETWEEN '2024-05-01' AND '2024-11-30';
Explanation:
Combines system-time and application-time conditions for detailed queries.
Use Cases of Validity Periods
Audit Trails
Track changes for compliance and troubleshooting.
Historical Analysis
Retrieve and analyze past states of data.
Regulatory Compliance
Ensure legal requirements for data retention and accuracy are met.
Best Practices
- Index Temporal Columns: Improves performance of range queries.
- Use UTC for Timestamps: Standardizes time zone differences.
- Archive Old Data: Manage table size and maintain query efficiency.
Validity periods in temporal tables provide a robust framework for managing time-sensitive data. By understanding system-time and application-time validity, and combining them for bi-temporal management, you can ensure comprehensive data auditing, analysis, and compliance. Happy Coding!❤️