Temporal Tables: Automatic Row History – SQL Server | Extra 4

Published / updated4 Oct 2026

Module 5 · Extra 4Advanced35 minInterview:FMSA

Before this lesson: Triggers for Validation and Audit Logs – SQL Server, MERGE and Upserts: Insert, Update, Delete in One Step – SQL Server | Extra 2

Introduction

A normal table remembers only the latest value. When the Class 10 tuition changes from 42,000 to 44,100, the old amount is gone. In Lesson 23, you wrote a trigger to keep old marks in an audit log.

A temporal table does this automatically, for every row and every column. SQL Server keeps each old version in a history table and lets you query the table as it was at any moment.

By the end of this lesson, you will be able to:

• Turn on system versioning for an existing table, and turn it off again.
• Query all versions of a row with FOR SYSTEM_TIME ALL.
• Query the whole table at a past moment with FOR SYSTEM_TIME AS OF.
• Explain how temporal tables compare with audit triggers and change data capture.

Why this matters

Fee disputes are common. A parent says the tuition was lower when they paid. The office changed it twice since then, and nobody remembers the exact dates.

With a temporal table, one query answers "what was the fee at 10:30 on that day?" No trigger to write, no audit table to design, and nobody can edit the history afterwards.

The concept

Example: how it works

A temporal table has two extra columns that SQL Server fills by itself:

Column	Meaning
ValidFrom	When this version of the row started
ValidTo	When it was replaced; 9999-12-31 for the current version

On every UPDATE or DELETE, SQL Server copies the old version into the history table and sets its ValidTo. The main table keeps only current rows, so normal queries stay fast.

Think of the school notice board. When the fee notice changes, the clerk does not throw the old notice away. She stamps it with today's date and files it, and the new notice goes up.

Example: the query forms

You write	You get
FROM t	Current rows only, as always
FROM t FOR SYSTEM_TIME AS OF @moment	The table exactly as it was at that moment
FROM t FOR SYSTEM_TIME ALL	Every version, current and old
FROM t FOR SYSTEM_TIME BETWEEN @a AND @b	Versions that were valid at some point in that range

The times are in UTC (Coordinated Universal Time), because SYSUTCDATETIME() sets them. Convert your local time to UTC before asking "as of 10:30".

Apply it to the SMS

The script turns on versioning for FeeStructures. The accounts user then raises Class 10 tuition by mistake and corrects it, so current fees end exactly as they started.

Show extras/e4-temporal-tables.sql

extras/e4-temporal-tables.sql

-- SQL Extra 4: temporal tables.
-- Turns on system versioning for FeeStructures (once), then changes Class 10 tuition and
-- changes it back, so the current fees end exactly as they started.
-- To remove versioning again, run extras/e4-temporal-tables-undo.sql.
USE SchoolDb;
GO

SET NOCOUNT ON;
GO

-- 1. Add the two period columns and turn on versioning (skipped if already on).
IF NOT EXISTS (
    SELECT 1 FROM sys.tables
    WHERE object_id = OBJECT_ID('dbo.FeeStructures')
      AND temporal_type = 2  -- 2 = system-versioned
)
BEGIN
    ALTER TABLE dbo.FeeStructures ADD
        ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
            CONSTRAINT DF_FeeStructures_ValidFrom DEFAULT SYSUTCDATETIME(),
        ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
            CONSTRAINT DF_FeeStructures_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

    ALTER TABLE dbo.FeeStructures
        SET (SYSTEM_VERSIONING = ON (
            HISTORY_TABLE = dbo.FeeStructuresHistory,
            HISTORY_RETENTION_PERIOD = 7 YEARS
        ));
END;

SELECT name, temporal_type_desc
FROM sys.tables
WHERE name IN ('FeeStructures', 'FeeStructuresHistory')
ORDER BY name;
GO

-- 2. The accounts user raises Class 10 tuition by mistake, then corrects it.
--    The moments are saved in a temp table so later batches can use them.
DROP TABLE IF EXISTS #Moments;
CREATE TABLE #Moments (Label VARCHAR(20) PRIMARY KEY, At DATETIME2 NOT NULL);

INSERT INTO #Moments (Label, At) VALUES ('BeforeChanges', SYSUTCDATETIME());
WAITFOR DELAY '00:00:01';

UPDATE dbo.FeeStructures
SET Amount = 44100, UpdatedAt = SYSUTCDATETIME(), UpdatedBy = 4
WHERE AcademicYearId = 2 AND ClassId = 10 AND FeeType = 'Tuition';

WAITFOR DELAY '00:00:01';
INSERT INTO #Moments (Label, At) VALUES ('AfterRaise', SYSUTCDATETIME());
WAITFOR DELAY '00:00:01';

UPDATE dbo.FeeStructures
SET Amount = 42000, UpdatedAt = SYSUTCDATETIME(), UpdatedBy = 4
WHERE AcademicYearId = 2 AND ClassId = 10 AND FeeType = 'Tuition';
GO

-- 3. Today's value: a normal query sees only current rows.
SELECT FeeType, Amount
FROM dbo.FeeStructures
WHERE AcademicYearId = 2 AND ClassId = 10 AND FeeType = 'Tuition';

-- 4. Every version since the changes began. Hidden columns must be named to be shown.
SELECT Amount, UpdatedBy, ValidFrom, ValidTo
FROM dbo.FeeStructures FOR SYSTEM_TIME ALL
WHERE AcademicYearId = 2 AND ClassId = 10 AND FeeType = 'Tuition'
  AND ValidTo > (SELECT At FROM #Moments WHERE Label = 'BeforeChanges')
ORDER BY ValidFrom;

-- 5. The value at a moment in the past.
DECLARE @AfterRaise DATETIME2 = (SELECT At FROM #Moments WHERE Label = 'AfterRaise');
DECLARE @BeforeChanges DATETIME2 = (SELECT At FROM #Moments WHERE Label = 'BeforeChanges');

SELECT 'Before changes' AS AsOf, Amount
FROM dbo.FeeStructures FOR SYSTEM_TIME AS OF @BeforeChanges
WHERE AcademicYearId = 2 AND ClassId = 10 AND FeeType = 'Tuition'
UNION ALL
SELECT 'After the raise', Amount
FROM dbo.FeeStructures FOR SYSTEM_TIME AS OF @AfterRaise
WHERE AcademicYearId = 2 AND ClassId = 10 AND FeeType = 'Tuition';

-- 6. The whole fee list as it was at that moment, in one query.
SELECT COUNT(*) AS FeeRows, SUM(Amount) AS TotalOfAllFees
FROM dbo.FeeStructures FOR SYSTEM_TIME AS OF @AfterRaise;
GO

-- 7. History is protected: it cannot be edited.
--    Expected: Msg 13561, Cannot update rows in a temporal history table.
UPDATE dbo.FeeStructuresHistory SET Amount = 1;
GO

This script changes the table, unlike the earlier extras. To remove versioning, run the undo script:

Show extras/e4-temporal-tables-undo.sql

extras/e4-temporal-tables-undo.sql

-- SQL Extra 4 (undo): turn system versioning off for FeeStructures and remove what
-- e4-temporal-tables.sql added. Current fees are not touched. Safe to run more than once.
USE SchoolDb;
GO

IF EXISTS (
    SELECT 1 FROM sys.tables
    WHERE object_id = OBJECT_ID('dbo.FeeStructures')
      AND temporal_type = 2
)
BEGIN
    ALTER TABLE dbo.FeeStructures SET (SYSTEM_VERSIONING = OFF);
END;

IF OBJECT_ID('dbo.FeeStructuresHistory', 'U') IS NOT NULL
    DROP TABLE dbo.FeeStructuresHistory;

IF EXISTS (SELECT 1 FROM sys.periods WHERE object_id = OBJECT_ID('dbo.FeeStructures'))
    ALTER TABLE dbo.FeeStructures DROP PERIOD FOR SYSTEM_TIME;

IF COL_LENGTH('dbo.FeeStructures', 'ValidFrom') IS NOT NULL
BEGIN
    ALTER TABLE dbo.FeeStructures DROP CONSTRAINT DF_FeeStructures_ValidFrom, DF_FeeStructures_ValidTo;
    ALTER TABLE dbo.FeeStructures DROP COLUMN ValidFrom, ValidTo;
END;

SELECT name, temporal_type_desc
FROM sys.tables
WHERE name LIKE 'FeeStructures%';
GO

Example: turning versioning on

Part 1 does two things. It adds the period columns, then switches versioning on with a named history table and a retention period:

name	temporal_type_desc
FeeStructures	SYSTEM_VERSIONED_TEMPORAL_TABLE
FeeStructuresHistory	HISTORY_TABLE

HIDDEN keeps the new columns out of SELECT *. Existing queries and the C# code you write later see no difference.

DEFAULT values are needed because the table already has 30 rows. Each existing row starts its history at the moment versioning was turned on.

Example: every version of a row

Part 4 lists the Class 10 tuition versions. Times are UTC from one run; yours will differ:

Amount	UpdatedBy	ValidFrom	ValidTo
42000.00	NULL	2026-10-04 05:45:58.5684732	2026-10-04 05:45:59.6068273
44100.00	4	2026-10-04 05:45:59.6068273	2026-10-04 05:46:01.6291504
42000.00	4	2026-10-04 05:46:01.6291504	9999-12-31 23:59:59.9999999

Each ValidTo equals the next ValidFrom, so there are no gaps. UpdatedBy is 4, the accounts user: the temporal table versions your audit columns too. That is how you answer "who" as well as "when".

Example: the table at a past moment

Part 5 asks for the tuition at two saved moments:

AsOf	Amount
Before changes	42000.00
After the raise	44100.00

Part 6 asks for the whole fee list at the "after the raise" moment: 30 rows totalling 379,100. Today's total is 377,000; the difference is exactly the 2,100 raise.

AS OF works across joins too. Every temporal table in a query can use the same moment, which rebuilds a whole screen as it looked on that day.

Example: history cannot be edited

Part 7 tries to change the history table:

Output

Msg 13561, Level 16, State 1
Cannot update rows in a temporal history table 'SchoolDb.dbo.FeeStructuresHistory'.

This error appears when the statement is compiled, so TRY...CATCH in the same batch cannot catch it. Deleting from history is blocked the same way. Only turning versioning off allows changes, and that needs ALTER TABLE permission, which the application never has (Lesson 28).

Example: what DELETE does

When a row is deleted from a temporal table, it disappears from normal queries. Its last version moves to the history table, so AS OF an earlier moment still shows it.

In the SMS, rows are soft-deleted with IsDeleted (Lesson 14), so this matters most for tables without soft delete.

Example: three ways to keep history

	Audit trigger (Lesson 23)	Temporal table	Change data capture
Setup	Write and maintain a trigger	One ALTER TABLE	Enable on database and table
What it keeps	What you choose, such as old and new marks	Every column, every version	Every change, read from the log
Query the past	Search the log yourself	FOR SYSTEM_TIME AS OF	Change functions, for export
Typical use	Custom audit, such as "who changed marks"	"What was the value on that day?"	Feeding other systems
Needs SQL Server Agent	No	No	Yes, so not on Express or LocalDB

The SMS keeps the marks trigger, because it stores exactly what the school's audit rule asks for. Fees get a temporal table, because the question there is "what was it on that day?"

Example: retention and growth

HISTORY_RETENTION_PERIOD = 7 YEARS tells SQL Server to remove history older than seven years automatically. Without a retention period, history grows forever.

Fees change a few times a year, so the history stays tiny. Versioning a table that changes every second, such as attendance, would grow quickly. Choose tables where the history answers a real question.

Common mistakes

1. Using local time with AS OF. The answer is off by hours. Fix: convert to UTC.
2. Versioning busy tables without a retention period. History grows without limit. Fix: set HISTORY_RETENTION_PERIOD, and version only where history is needed.
3. Expecting temporal tables to record who changed a row. They record when. Fix: keep UpdatedBy in the row, so it is versioned too.
4. Changing a table's design while versioning is on, without testing. Most column changes work, but some need versioning turned off first. Fix: test on a copy.
5. Forgetting to name hidden columns. SELECT * does not show ValidFrom. Fix: list them explicitly.

In real projects

From 16 years of .NET projects

I once spent two days rebuilding a fee history from invoice copies and emails, because a parent disputed a charge from the year before. The fee table had only current values.

On the next project, every price, fee, and rate table was temporal from day one. The same kind of dispute took one query and five minutes.

My rule now: if someone might ask "what was it on that date?", version the table. It costs one ALTER TABLE and almost no storage.

Best practices

• Version tables whose past values matter: prices, fees, rates, and settings.
• Keep UpdatedBy in the row so history also shows who made each change.
• Set a retention period that matches your legal or business rule.
• Make period columns HIDDEN so existing queries and code are unaffected.
• Use temporal tables for "as of" questions, and triggers for custom audit rules.

Recap

• A temporal table keeps every old version of every row in a history table.
• ValidFrom and ValidTo are filled by SQL Server, in UTC.
• FOR SYSTEM_TIME AS OF shows the table at any past moment; ALL shows every version.
• History cannot be edited while versioning is on (error 13561).
• Retention removes old history automatically.
• Temporal tables answer "when"; UpdatedBy answers "who".

Homework

Library module task: Turn on versioning for Books. Change the category of Malgudi Days to Short Stories, wait one second, and change it back to Fiction. List every version of the book, then turn versioning off again.

A correct result looks like: three versions of Malgudi Days, Fiction, Short Stories, then Fiction, where each ValidTo equals the next ValidFrom. At the end, Books is a normal table again.

Show the solution

library-assignment/e4-library-temporal.sql

-- Library homework, Extra 4: keep a history of book details with a temporal table.
-- Turns versioning on for Books, changes a category and changes it back, reads the history,
-- then removes versioning again (part 4), so the script leaves Books as it found it.
USE SchoolDb;
GO

SET NOCOUNT ON;
GO

-- 1. Turn on versioning.
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('dbo.Books') AND temporal_type = 2)
BEGIN
    ALTER TABLE dbo.Books ADD
        ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
            CONSTRAINT DF_Books_ValidFrom DEFAULT SYSUTCDATETIME(),
        ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
            CONSTRAINT DF_Books_ValidTo DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

    ALTER TABLE dbo.Books SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.BooksHistory));
END;
GO

-- 2. A librarian recategorizes Malgudi Days, then changes it back.
UPDATE dbo.Books SET Category = N'Short Stories', UpdatedAt = SYSUTCDATETIME() WHERE Isbn = '9780143039655';
WAITFOR DELAY '00:00:01';
UPDATE dbo.Books SET Category = N'Fiction', UpdatedAt = SYSUTCDATETIME() WHERE Isbn = '9780143039655';
GO

-- 3. Every version of the book.
SELECT Title, Category, ValidFrom, ValidTo
FROM dbo.Books FOR SYSTEM_TIME ALL
WHERE Isbn = '9780143039655'
ORDER BY ValidFrom;
GO

-- 4. Undo: turn versioning off and remove the history table and period columns.
ALTER TABLE dbo.Books SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.BooksHistory;
ALTER TABLE dbo.Books DROP PERIOD FOR SYSTEM_TIME;
ALTER TABLE dbo.Books DROP CONSTRAINT DF_Books_ValidFrom, DF_Books_ValidTo;
ALTER TABLE dbo.Books DROP COLUMN ValidFrom, ValidTo;

SELECT name, temporal_type_desc FROM sys.tables WHERE name = 'Books';
GO

Turning versioning off takes four steps in order: switch it off, drop the history table, drop the period, and drop the columns with their defaults.

Quiz

1. Where does SQL Server keep old versions of rows in a temporal table?

   1. In the transaction log only
   2. In a history table
   3. In tempdb
   4. In the main table, marked as old
   Show answer

   Answer: B. In a history table The main table keeps current rows; the history table keeps old versions.

2. Which query returns the fee list exactly as it was at a past moment?

   1. FOR SYSTEM_TIME ALL
   2. FOR SYSTEM_TIME AS OF @moment
   3. WHERE UpdatedAt = @moment
   4. SELECT * FROM FeeStructuresHistory
   Show answer

   Answer: B. FOR SYSTEM_TIME AS OF @moment AS OF rebuilds the table at that moment from current and history rows.

3. In which time zone are ValidFrom and ValidTo stored?

   1. The server's local time
   2. UTC
   3. The user's time zone
   4. None; they are dates only
   Show answer

   Answer: B. UTC SQL Server sets them with UTC time.

4. Someone tries to UPDATE the history table. What happens?

   1. It works
   2. Error 13561: history cannot be updated
   3. The main table changes too
   4. Versioning turns off
   Show answer

   Answer: B. Error 13561: history cannot be updated History is protected while versioning is on.

5. Which history method needs SQL Server Agent?

   1. Audit trigger
   2. Temporal table
   3. Change data capture
   4. All three
   Show answer

   Answer: C. Change data capture Change data capture uses Agent jobs to read the log; Express and LocalDB have no Agent.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is a temporal (system-versioned) table? concept

A table for which SQL Server automatically keeps every old version of every row in a history table. Two period columns, ValidFrom and ValidTo, record when each version was valid.

See the example · Permalink

F How do you see a table as it was at a past moment? coding

Query it with FOR SYSTEM_TIME AS OF @moment. SQL Server combines current and history rows to rebuild the table at that moment. FOR SYSTEM_TIME ALL returns every version.

See the example · Permalink

F In which time zone are a temporal table's period columns stored? concept

UTC. SQL Server fills them itself, so a local time must be converted to UTC before using it in AS OF.

See the example · Permalink

Mid (3-5 yrs)

M How do you turn an existing table into a temporal table? coding

Add two DATETIME2 columns GENERATED ALWAYS AS ROW START and ROW END with defaults and a PERIOD FOR SYSTEM_TIME, then SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ...)). HIDDEN keeps the new columns out of SELECT *.

See the example · Permalink

M Can you edit or delete rows in a temporal history table? concept

Not while versioning is on: updates fail with error 13561 and deletes are blocked too. Only someone with ALTER TABLE permission can turn versioning off, which makes the history trustworthy.

See the example · Permalink

M Does a temporal table record who changed a row? concept

No, only when. Keep an UpdatedBy column in the row; because it is versioned with the rest of the row, the history then shows who made each change.

See the example · Permalink

Senior (6-9 yrs)

S A parent disputes last term's fee. How would temporal tables help, and how would you answer them? scenario

Query the fee table FOR SYSTEM_TIME AS OF the payment time, converted to UTC, and show the amount that was valid then, plus who changed it and when from the versions list. The history cannot be edited, so the answer is reliable.

How to answer: Walk through the query, the UTC conversion, and how you present the evidence. Mention that without versioning you would depend on invoices and emails.

See the example · Permalink

S Compare audit triggers, temporal tables, and change data capture. concept

Audit triggers keep exactly what you choose and need code. Temporal tables keep every version automatically and answer "as of" questions. Change data capture reads changes from the log for other systems and needs SQL Server Agent.

How to answer: Compare setup, storage, query style, and requirements, then pick per need: temporal for prices and settings, triggers for custom audit rules, CDC for data feeds.

See the example · Permalink

Architect / Lead (10+ yrs)

A Which tables in a multi-school SaaS would you make temporal, and how would you manage history growth? design

Version tables whose past values matter and change rarely: fees, prices, rates, and settings. Avoid busy tables like attendance. Set a retention period that matches the legal rule, watch history size, and include history tables in backup and archiving plans.

How to answer: Tie each choice to a business question ("what was it on that date?"), then cover cost, retention, compliance, and operations.

See the example · Permalink

Resources

• Code for this lesson: git tag extra-4-temporal-tables (code repository coming soon)
• Files: extras/e4-temporal-tables.sql, extras/e4-temporal-tables-undo.sql, library-assignment/e4-library-temporal.sql
• Video: coming soon

What's next

Your database now keeps its own history. This completes the four published SQL Extras. Maintenance with SQL Server Agent jobs and table partitioning are planned next.

You can now revise the complete SQL path in Lesson 29: SQL interview practice, or continue with Lesson 30: The .NET platform when you are ready for C#.

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.