How to create temporal tables in SQL Server: step by step

João Barros 07 de July de 2026 4 min read

Temporal tables (system-versioned) in SQL Server automatically keep the history of every row and let you query the data exactly as it was at any point in the past. They are perfect for auditing, change analysis and recovering values that were changed by mistake, without writing triggers or maintaining history tables by hand. It is one of the simplest ways to add traceability and governance to an existing data model.

Prerequisites

SQL Server 2016 or later, or a database in Azure SQL Database.
SQL Server Management Studio (SSMS) or Azure Data Studio to run the queries.
Permissions to create tables in a test database (avoid production).
Basic T-SQL knowledge: CREATE TABLE, INSERT, UPDATE and SELECT.

Step 1: Create the temporal table

A temporal table needs three things: a primary key, two datetime2 columns that mark the start and end of validity for each row version, and the PERIOD FOR SYSTEM_TIME clause. The period columns are filled in by the engine (note the GENERATED ALWAYS AS ROW START/END), so you never write values to them. Unlike a trigger-based solution, versioning is managed by SQL Server itself. We turn it on with SYSTEM_VERSIONING = ON and give the history table a name.

CREATE TABLE dbo.Funcionario
(
    FuncionarioID INT           NOT NULL PRIMARY KEY CLUSTERED,
    Nome          NVARCHAR(100) NOT NULL,
    Departamento  NVARCHAR(50)  NOT NULL,
    Salario       DECIMAL(10,2) NOT NULL,
    ValidoDe  DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidoAte DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidoDe, ValidoAte)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.FuncionarioHistorico));

SQL Server automatically creates the dbo.FuncionarioHistorico table with the same structure. That is where old versions of each row are transparently stored. Period values are recorded in UTC.

The history table only grows. In real scenarios, set a retention policy (HISTORY_RETENTION_PERIOD) to automatically delete old versions and keep storage under control.

Step 2: Insert and change data

Notice that we do not provide values for the period columns — the engine handles that. Let's insert an employee and, later, give them a raise.

INSERT INTO dbo.Funcionario (FuncionarioID, Nome, Departamento, Salario)
VALUES (1, N'Ana Silva', N'Dados', 3000.00);

-- Some time later, Ana gets a raise:
UPDATE dbo.Funcionario
SET Salario = 3500.00
WHERE FuncionarioID = 1;

At the moment of the UPDATE, the previous version (salary 3000) is copied to the history table with the validity interval during which it was active, and the current row keeps the new value.

Step 3: Query the history with FOR SYSTEM_TIME

A normal query returns only the current state. To see how the data looked at a specific instant, use FOR SYSTEM_TIME AS OF:

-- Replace with a real date/time between the insert and the raise
SELECT FuncionarioID, Nome, Salario, ValidoDe, ValidoAte
FROM dbo.Funcionario
FOR SYSTEM_TIME AS OF '2026-07-07 15:00:00'
WHERE FuncionarioID = 1;

SQL Server returns the version that was valid at that moment — even if it has since been replaced. To see all versions of a row, current and historical, at once, use ALL:

SELECT FuncionarioID, Nome, Salario, ValidoDe, ValidoAte
FROM dbo.Funcionario
FOR SYSTEM_TIME ALL
WHERE FuncionarioID = 1
ORDER BY ValidoDe;

Step 4: Turn versioning off safely

You cannot drop or change the structure of a temporal table directly. First you turn versioning off; the link to the history table is removed, but the data stays intact.

ALTER TABLE dbo.Funcionario SET (SYSTEM_VERSIONING = OFF);
-- You can now alter columns or drop tables if needed.
-- To re-enable, point again to the same history table.

Check the result

A simple query (SELECT * FROM dbo.Funcionario) should show only the current version, with salary 3500. If the FOR SYSTEM_TIME ALL query returns two rows for FuncionarioID 1 — one with 3000 and another with 3500 — versioning is working. In SSMS, the table appears with a clock icon and, when expanded, shows the associated history table underneath.

Conclusion

With just a few lines of T-SQL you now have an automatic, foolproof history, ready for audits and for answering the question "what did this look like before?". The next step is to explore the FROM ... TO, BETWEEN ... AND and CONTAINED IN variants to query time ranges, and to set an appropriate retention policy. Which table in your model would already benefit from keeping its own history?