Temporal Tables

By the end of this lesson you'll be able to make the database keep a full, automatic history of every row — and then query "what did this look like last Tuesday?" with a single line. Temporal tables turn an ordinary table into a time machine you can rewind to any instant.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

1. What Is a Temporal Table?

An ordinary table only remembers the present . When you UPDATE a row, the old value is overwritten and gone forever. A system-versioned temporal table fixes that: the database keeps every previous version of every row automatically, in a paired history table , so you can look up any past state on demand.

A normal table is a whiteboard — erase and rewrite, and the old text is lost. A temporal table is a notebook : every edit starts a fresh line, and the previous lines stay on the page. You can always flip back to see what was written on any date.

"System-versioning" means the system (the DB engine), not you, manages the versioning. You keep writing normal INSERT , UPDATE , and DELETE statements; the engine quietly files away each superseded row for you.

2. Two Kinds of Time: Transaction-Time vs Valid-Time

"Temporal" is an umbrella term. There are two distinct clocks, and mixing them up is the most common beginner mistake:

• Transaction-time (a.k.a. system-time): when the database stored the row. This is what system-versioning tracks automatically with valid_from / valid_to columns you never touch. It answers "what did our records say on date X?"
• Valid-time (a.k.a. application-time): when a fact is true in the real world. You set these dates yourself — like a subscription that runs from June to December. It answers "what was actually in effect on date X?"

3. The History Table Mirrors the Main Table

When you switch on SYSTEM_VERSIONING , the engine creates (or attaches) a second table — here employees_history — with the exact same columns as employees . The two stay in lockstep: add a column to the main table and the history table gains it too.

The split is simple: employees holds only the current version of each row; employees_history holds every superseded version. Each row carries a valid_from and valid_to stamp marking the window during which it was the live row.

4. Watch One Row Change Over Time

Let's follow employee 7, Alice. You write three ordinary statements — one insert and two updates — and the engine records each version behind the scenes. You never write to the history table directly.

After those three statements, the full timeline of Alice's row looks like this. Each version's valid_from / valid_to shows exactly when it was the live row, and the newest one runs to the engine's "max date":

The top two rows live in employees_history ; the bottom row (ending 9999-12-31) is the current row in employees . Notice how each valid_to matches the next row's valid_from — there are no gaps.

5. Reading the Full History with FOR SYSTEM_TIME ALL

You don't have to query the history table by name. Add FOR SYSTEM_TIME ALL to a query on the main table and the engine transparently returns the current row plus every historical version, all with the same columns.

Your Turn: time-travel to a date

Fill in the blanks to see Alice's row as it stood on 1 February 2024. The expected result is in the comments so you can check yourself.

6. Time-Travel with FOR SYSTEM_TIME AS OF

This is the headline feature. FOR SYSTEM_TIME AS OF '<timestamp>' rewinds the entire table to a single instant and shows the version of each row that was live then. It's how you answer "what did this row look like last Tuesday?"

The query reads from employees as if it were a normal SELECT , but the engine silently swaps in the right historical row. Your application code barely changes — you just bolt FOR SYSTEM_TIME AS OF onto the table.

At exactly 09:00 on 1 April the raise to 60,000 had just taken effect, but the move to Marketing hadn't — so you get the middle version, not today's 65,000.

7. Range Queries: BETWEEN and FROM..TO

Beyond a single instant, you can grab every version that was active across a window of time:

• FOR SYSTEM_TIME BETWEEN 'a' AND 'b' — every version active at any point in the range, inclusive of both ends.
• FOR SYSTEM_TIME FROM 'a' TO 'b' — the same idea, but exclusive of the end instant b .
• FOR SYSTEM_TIME CONTAINED IN ('a', 'b') — only versions that opened and closed entirely inside the window.

Your Turn: list one row's change history

Two blanks this time — return the complete change history of employee 7, oldest version first.

8. Valid-Time (Application-Time) by Hand

Sometimes you care about real-world validity, not storage time — insurance policies, hotel bookings, subscription plans. Here you own the dates, and you query them with a plain WHERE — no FOR SYSTEM_TIME , because the database isn't managing this clock.

Use 9999-12-31 (or 'infinity' on engines that support it) as the valid_to of the current, open-ended period. Then "what applies on date X?" is just valid_from <= X AND valid_to > X .

Where Temporal Tables Earn Their Keep

• Audit trails: a complete, tamper-evident record of who-had-what-when, for free — no bespoke logging code.
• Regulatory compliance: answer "prove what data you held on 15 March" with one AS OF query.
• Time-travel / bug investigation: reproduce "what did this customer's account look like when the error fired?"
• Slowly-changing dimensions: warehouses get point-in-time history without hand-rolled SCD plumbing.
• Accidental-change recovery: a bad bulk update is easy to inspect and reconstruct from history.

Common Errors (and the fix)

• Confusing valid-time with transaction-time: if a fact is true from Monday but you only enter it on Friday, system-time records Friday . Don't use FOR SYSTEM_TIME AS OF to ask "what was true in reality" — that's valid-time, which you model with your own date columns.
• Trying to delete or edit history: the engine guards the history table. DELETE FROM employees_history fails while versioning is on. To prune old rows you must first ALTER TABLE employees SET (SYSTEM_VERSIONING = OFF) — which removes the automatic protection, so do it deliberately.
• Writing to valid_from / valid_to : those columns are GENERATED ALWAYS . INSERT / UPDATE them and you get an error — the engine owns those values.
• Engine differences: the syntax is standard, but MySQL and PostgreSQL have no built-in support. Run FOR SYSTEM_TIME there and it's a syntax error — you'd need a trigger-based history table instead.
• Forgetting AS OF is a single instant: AS OF '2024-06-15' means midnight on that day. If a change happened later that afternoon, you'll see the before value — add a time component when precision matters.

📘 Quick Reference

Syntax

Purpose

Frequently Asked Questions

No — that's the whole point of system -versioning. You keep writing normal INSERT / UPDATE / DELETE , and the engine files away each old version automatically. Manual triggers are only needed on engines without built-in support (MySQL, PostgreSQL).

Q: What's the difference between system-time and valid-time?

System-time (transaction-time) is when the database stored the row and is auto-managed. Valid-time (application-time) is when a fact is true in the real world and you set it yourself. A table tracking both is called bitemporal .

Q: Does keeping history slow my normal queries down?

Plain SELECT s read only the small current table, so they're unaffected. History lives in a separate table that's touched only when you add FOR SYSTEM_TIME . It does use more storage, so index and occasionally archive the history table on busy systems.

Not with this exact syntax — neither has built-in temporal tables yet. On those you simulate it: add valid_from / valid_to columns and a trigger that copies the old row into a history table on every update. SQL Server 2016+, MariaDB 10.3+, Db2, and Oracle support it natively.

Mini-Challenge: Investigate Alice's Record

Put it all together — a brief, a blank canvas, and the expected results in the comments. Write the two queries, then run them on a SQL Server or MariaDB instance to confirm.

🎉 Lesson Complete

• ✅ A system-versioned temporal table keeps a full history of every row automatically
• ✅ Transaction-time (system) is DB-managed; valid-time (application) is yours to set
• ✅ FOR SYSTEM_TIME AS OF rewinds the whole table to a single instant
• ✅ ALL , BETWEEN , and FROM..TO pull ranges of versions from history
• ✅ The history table mirrors the main table's columns and is guarded by the engine
• ✅ Next: Full-Text Search — find rows by words and phrases, not exact matches

Practice quiz

Continue this course

• Previous: Recursive CTEs
• Next: Full-Text Search

Related lessons

• SELECT
• WHERE filtering