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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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

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.

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.

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.

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.

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.

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

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

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 .

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary