Sign in to track progress and bookmarks.
How do you know what a user's address was three years ago? Historically, we built complex Audit tables and Triggers. System-Versioned Temporal Tables allow SQL Server to handle all of this automatically, keeping a full history of every change ever made.
When you enable system-versioning, SQL Server creates a hidden **History Table**. Whenever you update or delete a row in the main table, the old version is automatically moved to the history table with 'ValidFrom' and 'ValidTo' timestamps.
Querying history is easy. You can ask SQL Server: "Show me the users as they existed on January 1st, 2022."
SELECT * FROM Users
FOR SYSTEM_TIME AS OF '2022-01-01 00:00:00'
WHERE Id = 5Q: "Does a Temporal Table impact performance?"
Architect Answer: "For `SELECT` queries on the current data, there is zero impact. For `INSERT`, `UPDATE`, and `DELETE`, there is a tiny overhead as SQL Server must write to two tables (Main and History). You must also be careful with disk space—history tables grow forever. Professional architects usually implement a 'Retention Policy' to purge history older than 7 years for compliance."
Quizzes linked to this course—pass to earn certificates.
On this page
1. How it Works 2. The FOR SYSTEM_TIME Clause 4. Interview Mastery