Temporal Tables (Time Travel SQL)

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.

1. How it Works

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.

2. The FOR SYSTEM_TIME Clause

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 = 5

4. Interview Mastery

Q: "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."