Senior LevelSQL & MySQL
How do Flashback / Time-Travel queries read historical data versions using System Versioned tables?
❓ Interview Prompt: Explain System-Versioned Tables (ANSI SQL:2011), `FOR SYSTEM_TIME AS OF`, historical versioning, and auditing.
System-Versioned Tables (ANSI SQL:2011 standard) automatically track historical row versions using system start and end timestamps:
Time-Travel Queries:
Queries can inspect data exactly as it existed at any historical point in time using 'FOR SYSTEM_TIME AS OF' without restoring database backups!
Code Implementation Example
sql
-- Creating a System-Versioned Table
CREATE TABLE accounts (
id INT PRIMARY KEY,
balance DECIMAL(10, 2),
sys_start TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
sys_end TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(sys_start, sys_end)
) WITH SYSTEM VERSIONING;
-- Querying historical balance as it existed 1 hour ago!
SELECT * FROM accounts FOR SYSTEM_TIME AS OF NOW() - INTERVAL 1 HOUR;💡Querying historical table versions using ANSI SQL System Versioning.
🎯 Interview Key Takeaway
Use System-Versioned tables and FOR SYSTEM_TIME AS OF to execute point-in-time historical data analysis without backup restoration.
#SQL#Time Travel#System Versioning#Audit#Senior
Back to all SQL & MySQL Questions→