Temporal tables: history and point-in-time queries

“What did the drill cost last March?” “Who changed this price, and what was it before?” “Someone ran an UPDATE without a WHERE; put the prices back.” An ordinary table cannot answer these, because an UPDATE overwrites the old value. A temporal table (also called a system-versioned table) keeps every previous version of every row, automatically, and lets you query the table as it was at any moment.

Creating a temporal table

A temporal table needs two datetime2 columns that SQL Server fills in itself, a PERIOD FOR SYSTEM_TIME naming them, and SYSTEM_VERSIONING = ON. This price list starts with three products copied from BuildMart:

1CREATE TABLE dbo.PriceList (2    ProductId int           NOT NULL PRIMARY KEY,3    Name      nvarchar(60)  NOT NULL,4    Price     decimal(10,2) NOT NULL,5    ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,6    ValidTo   datetime2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,7    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)8)9WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PriceListHistory));10GO11INSERT INTO dbo.PriceList (ProductId, Name, Price)12SELECT ProductId, Name, Price FROM dbo.Products WHERE ProductId <= 3;

• ValidFrom (ROW START) is when this version of the row became current; ValidTo (ROW END) is when it stopped being current. A current row has ValidTo = 9999-12-31, the end of time.
• SQL Server sets both. You cannot insert or update them, so nobody can fake the history.
• HIDDEN keeps them out of SELECT *, so existing queries and INSERTs without a column list are not affected.
• HISTORY_TABLE names the table that receives old versions. SQL Server creates it with the same columns.

1SELECT * FROM dbo.PriceList;2 3SELECT ProductId, Price, ValidFrom, ValidTo FROM dbo.PriceList;

ProductId  Name              Price
---------  ----------------  -----
        1  Claw hammer       18.50
        2  Cordless drill    89.00
        3  Tape measure 8 m   9.75

ProductId  Price  ValidFrom                    ValidTo
---------  -----  ---------------------------  ---------------------------
        1  18.50  2026-11-09 20:31:50.6978749  9999-12-31 23:59:59.9999999
        2  89.00  2026-11-09 20:31:50.6978749  9999-12-31 23:59:59.9999999
        3   9.75  2026-11-09 20:31:50.6978749  9999-12-31 23:59:59.9999999

The times are the UTC time when the inserting transaction began; yours will be the moment you ran it. In the SSMS Object Explorer the table shows “(System-Versioned)” and has its history table nested under it.

Every change leaves a version behind

Now change the data normally. Each UPDATE or DELETE copies the old version of the row into the history table, with its ValidTo set to the time of the change:

1UPDATE dbo.PriceList SET Price = 19.50 WHERE ProductId = 1;2UPDATE dbo.PriceList SET Price = 20.00 WHERE ProductId = 1;3DELETE FROM dbo.PriceList WHERE ProductId = 3;4 5SELECT ProductId, Price FROM dbo.PriceList;6 7SELECT ProductId, Price, ValidFrom, ValidTo8FROM dbo.PriceListHistory9ORDER BY ProductId, ValidFrom;

ProductId  Price
---------  -----
        1  20.00
        2  89.00

ProductId  Price  ValidFrom                    ValidTo
---------  -----  ---------------------------  ---------------------------
        1  18.50  2026-11-09 20:31:51.1285141  2026-11-09 20:31:51.1462404
        1  19.50  2026-11-09 20:31:51.1462404  2026-11-09 20:31:51.1462404
        3   9.75  2026-11-09 20:31:51.1285141  2026-11-09 20:31:51.1462404

The current table has the hammer at 20.00 and no tape measure. The history table holds the hammer’s two earlier prices and the deleted tape measure. Each history row’s ValidTo is the next version’s ValidFrom, so the versions fit together end to end with no gaps.

Querying the past: FOR SYSTEM_TIME

You rarely read the history table directly. Add FOR SYSTEM_TIME after the table name, and SQL Server looks in both tables and returns the versions that match. AS OF gives the table as it was at one moment. Normally you would type a time, such as AS OF '2026-03-01 09:00'. Here the script saves the times in variables, so it works whenever you run it:

1DECLARE @BeforeRise datetime2 = SYSUTCDATETIME();2WAITFOR DELAY '00:00:00.100';3 4UPDATE dbo.PriceList SET Price = Price * 1.10;          -- a 10% price rise5 6SELECT ProductId, Name, Price FROM dbo.PriceList;7 8SELECT ProductId, Name, Price9FROM dbo.PriceList FOR SYSTEM_TIME AS OF @BeforeRise;

ProductId  Name              Price
---------  ----------------  -----
        1  Claw hammer       20.35
        2  Cordless drill    97.90
        3  Tape measure 8 m  10.73

ProductId  Name              Price
---------  ----------------  -----
        1  Claw hammer       18.50
        2  Cordless drill    89.00
        3  Tape measure 8 m   9.75

The first SELECT shows today’s prices; the second shows the prices just before the rise. AS OF returns each row that was current at that moment: ValidFrom <= @time AND ValidTo > @time. The short WAITFOR only makes sure the clock moves on before the UPDATE.

Ranges and ALL

ALL returns every version, current and historical, which is the easy way to see a row’s whole story. A window function puts the old price beside the new one:

1WAITFOR DELAY '00:00:00.100';      -- let the clock move on between changes2UPDATE dbo.PriceList SET Price = 19.50 WHERE ProductId = 1;3WAITFOR DELAY '00:00:00.100';4UPDATE dbo.PriceList SET Price = 20.00 WHERE ProductId = 1;5 6SELECT Name, Price,7       LAG(Price) OVER (ORDER BY ValidFrom) AS PreviousPrice,8       CASE WHEN ValidTo = '9999-12-31 23:59:59.9999999' THEN 'current' ELSE 'old' END AS Version9FROM dbo.PriceList FOR SYSTEM_TIME ALL10WHERE ProductId = 111ORDER BY ValidFrom;

Name         Price  PreviousPrice  Version
-----------  -----  -------------  -------
Claw hammer  18.50           NULL  old
Claw hammer  19.50          18.50  old
Claw hammer  20.00          19.50  current

Three more forms take a range of time. They differ only in how they treat versions that touch the edges of the range:

SELECT Price, ValidFrom, ValidTo
FROM dbo.PriceList FOR SYSTEM_TIME AS OF '2024-03-01'
WHERE ProductId = 1
ORDER BY ValidFrom;

-- Price  ValidFrom   ValidTo
-- 17.50  2024-03-01  2024-06-01

Undoing a mistake from history

Someone means to put the hammer on sale and forgets the WHERE clause. Every price in the list is now 9.99:

1DECLARE @BeforeMistake datetime2 = SYSUTCDATETIME();2WAITFOR DELAY '00:00:00.100';3 4UPDATE dbo.PriceList SET Price = 9.99;                  -- oops: no WHERE5 6UPDATE cur7SET Price = old.Price8FROM dbo.PriceList AS cur9JOIN dbo.PriceList FOR SYSTEM_TIME AS OF @BeforeMistake AS old10  ON old.ProductId = cur.ProductId;11 12SELECT ProductId, Name, Price FROM dbo.PriceList;

ProductId  Name              Price
---------  ----------------  -----
        1  Claw hammer       18.50
        2  Cordless drill    89.00
        3  Tape measure 8 m   9.75

The repair joins the table to itself as it was before the mistake and copies the old prices back. The repair is itself an UPDATE, so the history now records the mistake and the repair too. Nothing is ever lost, which is exactly what auditors want.

The rules that come with history

The history is protected. While system versioning is on, you cannot change it or drop the table:

1DELETE FROM dbo.PriceListHistory;2GO3DROP TABLE dbo.PriceList;

Msg 13560, Level 16, State 1, Line 1
Cannot delete rows from a temporal history table 'BuildMart.dbo.PriceListHistory'.
Msg 13552, Level 16, State 1, Line 1
Drop table operation failed on table 'BuildMart.dbo.PriceList' because it is not a supported operation on system-versioned temporal tables.

• Schema changes to the current table, such as ALTER TABLE … ADD, are copied to the history table for you.
• All rows changed in one transaction get the same time: the time the transaction began, not the moment of each statement. If a row changes twice in one transaction (or twice within the clock’s resolution), the version in between lasted no time at all. It stays in the history table, but FOR SYSTEM_TIME, even ALL, leaves it out.
• TRUNCATE TABLE is not allowed on a system-versioned table. Use DELETE, which keeps history.
• To change history on purpose (for example, to remove personal data), turn versioning off, change the history table, and turn it on again. Keep that permission for administrators only.

History grows for ever unless you limit it. A retention period makes SQL Server delete history rows older than that, in the background:

1ALTER TABLE dbo.PriceList2SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PriceListHistory,3                             HISTORY_RETENTION_PERIOD = 2 YEARS));4 5SELECT t.name, t.temporal_type_desc, h.name AS history_table,6       t.history_retention_period, t.history_retention_period_unit_desc7FROM sys.tables AS t8LEFT JOIN sys.tables AS h ON h.object_id = t.history_table_id9WHERE t.name = 'PriceList';

name       temporal_type_desc               history_table     history_retention_period  history_retention_period_unit_desc
---------  -------------------------------  ----------------  ------------------------  ----------------------------------
PriceList  SYSTEM_VERSIONED_TEMPORAL_TABLE  PriceListHistory                         2  YEAR

To get rid of a temporal table, switch versioning off first. The two tables then become ordinary tables you can drop:

1ALTER TABLE dbo.PriceList SET (SYSTEM_VERSIONING = OFF);2DROP TABLE dbo.PriceList, dbo.PriceListHistory;3 4SELECT COUNT(*) AS TablesLeft FROM sys.tables WHERE name LIKE 'PriceList%';

TablesLeft
----------
         0

1CREATE TABLE dbo.StockLevels (2    ProductId int NOT NULL PRIMARY KEY,3    Stock     int NOT NULL,4    ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,5    ValidTo   datetime2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,6    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)7)8WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.StockLevelsHistory));9GO10INSERT INTO dbo.StockLevels (ProductId, Stock)11SELECT ProductId, Stock FROM dbo.Products WHERE ProductId <= 3;12WAITFOR DELAY '00:00:00.050';13 14UPDATE dbo.StockLevels SET Stock = 35 WHERE ProductId = 1;15WAITFOR DELAY '00:00:00.050';16 17UPDATE dbo.StockLevels SET Stock = 50 WHERE ProductId = 1;18 19SELECT Stock,20       CASE WHEN ValidTo = '9999-12-31 23:59:59.9999999' THEN 'yes' ELSE 'no' END AS IsCurrent21FROM dbo.StockLevels FOR SYSTEM_TIME ALL22WHERE ProductId = 123ORDER BY ValidFrom;24 25ALTER TABLE dbo.StockLevels SET (SYSTEM_VERSIONING = OFF);26DROP TABLE dbo.StockLevels, dbo.StockLevelsHistory;

Stock  IsCurrent
-----  ---------
   40  no
   35  no
   50  yes