How to Retrieve All Versions of a Specific Row
Retrieving All Versions of a Specific Row
Write a SQL query to retrieve all versions of a specific row in a temporal table.
Solution:
-- Retrieve all versions of the row with EmployeeID = 1.
SELECT EmployeeID, Name, Position, SysStartTime, SysEndTime
FROM Employees
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1
ORDER BY SysStartTime;
Explanation:
- Purpose of the Query :
- The goal is to demonstrate how to query all versions of a specific row across its entire history.
- Key Components :
- FOR SYSTEM_TIME ALL: Retrieves all versions of rows from both the main table and history table.
- Filters by EmployeeID to focus on a specific record.
- Why Query All Versions? :
- Viewing all versions of a row provides a complete audit trail for that record.
- It helps identify when and why changes occurred.
- Real-World Application :
- In customer relationship management (CRM) systems, this tracks changes to customer profiles.
Additional Notes:
- Use this query for detailed record-level auditing.
- Order results by SysStartTime to view changes chronologically.
- Important Considerations:
- Filter by a unique identifier (e.g., primary key) to isolate specific rows.
For more Practice: Solve these Related Problems:
- Write a SQL query to retrieve all versions of a specific product with ProductID = 101 from a temporal Products table.
- Write a SQL query to fetch the complete history of an employee with EmployeeID = 5 from a temporal Employees table.
- Write a SQL query to track all changes made to a specific customer record in a temporal Customers table.
- Write a SQL query to analyze the full audit trail of an order with OrderID = 12345 from a temporal Orders table.
Go to:
PREV : Retrieving all Changes Between Two Points in Time.
NEXT : Disabling System-Versioning on 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.