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.

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

Previous SQL Exercise: Retrieving all Changes Between Two Points in Time.
Next SQL Exercise: Disabling System-Versioning on a Temporal Table.