In today's data-driven world, understanding how data changes over time is just as important as the data itself. Having a clear view of data changes over time is critical for compliance, auditing, historical analysis, or recovering from errors. That's where temporal tables come in.

Introduced in SQL Server 2016, temporal tables — also known as system-versioned temporal tables — offer a built-in solution for automatically tracking and storing the full history of data changes. This powerful feature allows users to query data as it existed at any specific time, without custom tracking or complex audit logs.

What's more, temporal tables integrate seamlessly with Entity Framework Core, enabling .NET developers to interact with historical data using familiar LINQ queries and model configurations, making it easy to implement robust auditing and time-travel capabilities directly within modern applications.

Why Use temporal tables?

  • Data Auditing: Track every change for compliance, security audits, and forensic analysis
  • Historical Data Analysis: Reconstruct the exact state of data at any past moment to uncover trends and insights
  • Error Recovery: Roll back accidental changes or deletions by accessing earlier versions of your data.

Getting Started

You can set up new system-versioned temporal tables or modify existing ones to incorporate temporal features.

To create a system-versioned temporal table in SQL Server you need to:

  • specify DATETIME2 columns that automatically track the validity window of each row (ex. ValidFrom and ValidTo)
  • define the table with PERIOD FOR SYSTEM_TIME
  • set WITH (SYSTEM_VERSIONING = ON) that enables system versioning and links a history table (ex. dbo.AccountHistory)

Here's an example:

CREATE TABLE dbo.Accounts (
	AccountId INT IDENTITY(1,1) PRIMARY KEY,
	FirstName NVARCHAR(100) NOT NULL,
	LastName NVARCHAR(100) NOT NULL,
	Country NVARCHAR(1024) NOT NULL,
	City NVARCHAR(1024) NOT NULL,
	Address NVARCHAR(1024) NOT NULL,
	ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
	ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
	PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AccountHistory));

For existing tables, system versioning can be enabled using the ALTER TABLE statement:

ALTER TABLE dbo.Accounts ADD
ValidFrom DATETIME2(2) GENERATED ALWAYS AS ROW START HIDDEN
	CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2(2) GENERATED ALWAYS AS ROW END HIDDEN
	CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE dbo.Accounts
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AccountHistory));

Understanding temporal tables

A temporal table consists of two parts:

  • Current Table (or temporal table): Holds the latest version of data, with ValidFrom and ValidTo columns defining the validity window. (ex. dbo.Accounts)
  • History Table: Automatically managed by SQL Server, this table stores historical versions of rows from the current table. Each time a row in the current table is updated or deleted, the previous version is preserved in the history table along with its validity window. (ex. dbo.AccountHistory)

This structure allows SQL Server to maintain a full history of data changes, facilitating point-in-time analysis.

Here's how the system handles actions behind the scenes:

  • INSERT: Sets ValidFrom to the start time of the current transaction (UTC) and ValidTo to the maximum value of '9999-12-31', marking the row as open.
  • UPDATE: The old version is saved to the history table with its ValidTo value set to the transaction's start time, indicating that the row is closed. Then, the current table is updated with the new values, ValidFrom is set to the transaction's start time while ValidTo remains unchanged.
  • DELETE: Moves deleted row to the history table with ValidTo set to the transaction's start time, marking the row as closed. In the current table the row is removed, making it non-existent for standard queries, but history-data queries do return it.

Querying temporal tables

You can use the SQL FOR SYSTEM_TIME clause to query historical data. When using this clause data will be retrieved from the temporal and history tables implicitly. There are five available commands:

  • ALL: Retrieve all versions of a row, including current and historical data. 
SELECT * FROM dbo.Accounts
FOR SYSTEM_TIME ALL
WHERE AccountId = 1;
  • AS OF: Retrieve data as it existed at a particular moment. A row will be returned if ValidFrom <= datetime AND ValidTo > datetime
SELECT * FROM dbo.Accounts
FOR SYSTEM_TIME AS OF '2025-01-01 12:00:00'
WHERE AccountId = 1;
  • FROM … TO: Retrieve rows that were valid during a specific time window. A row will be returned if ValidFrom < end_datetime AND ValidTo > start_datetime
SELECT * FROM dbo.Accounts
FOR SYSTEM_TIME FROM '2025-01-01 12:00:00' TO '2025-01-01 13:00:00'
WHERE AccountId = 1;
  • BETWEEN … AND: Retrieve rows that were valid during a specific time window, but unlike the FROM … TO clause, it includes rows that started exactly on end_datetime. A row will be returned if ValidFrom <= end_datetime AND ValidTo > start_datetime
SELECT * FROM dbo.Accounts
FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-01-31'
WHERE AccountId = 1;
  • CONTAINED IN: Retrieve rows that were opened and closed within a specific time range. Row will be returned if ValidFrom >= start_datetime AND ValidTo <= start_datetime
SELECT * FROM dbo.Accounts
FOR SYSTEM_TIME CONTAINED IN ('2025-01-01', '2025-01-31')
WHERE AccountId = 1;

If you need to retrieve only changed columns instead of full rows from historical tables, you can use the CROSS APPLY or UNPIVOT operators. 

SELECT 
	curr.AccountId,
	change.ColumnName,
	change.OldValue,
	change.NewValue,
	curr.ValidFrom,
	curr.ValidTo
FROM 
	Accounts FOR SYSTEM_TIME ALL AS curr
	INNER JOIN Accounts FOR SYSTEM_TIME ALL AS hist ON curr.AccountId = hist.AccountId AND hist.ValidTo = curr.ValidFrom
CROSS APPLY (
	VALUES
		('FirstName', CAST(hist.FirstName AS NVARCHAR(MAX)), CAST(curr.FirstName AS NVARCHAR(MAX))),
		('LastName', CAST(hist.LastName AS NVARCHAR(MAX)), CAST(curr.LastName AS NVARCHAR(MAX))),
		('Address', CAST(hist.Address AS NVARCHAR(MAX)), CAST(curr.Address AS NVARCHAR(MAX)))
) AS change (ColumnName, OldValue, NewValue)
WHERE 
	(change.OldValue IS NULL AND change.NewValue IS NOT NULL)
	OR (change.OldValue IS NOT NULL AND change.NewValue IS NULL)
	OR (change.OldValue <> change.NewValue)
ORDER BY 
	curr.AccountId, 
	curr.ValidFrom;
WITH ChangedRows AS (
	SELECT 
		h.AccountId,
		h.ValidFrom AS [From],
		h.ValidTo AS [To],
		CAST(h.FirstName AS NVARCHAR(MAX)) AS Old_FirstName, 
		CAST(c.FirstName AS NVARCHAR(MAX)) AS New_FirstName,
		CAST(h.LastName AS NVARCHAR(MAX)) AS Old_LastName, 
		CAST(c.LastName AS NVARCHAR(MAX)) AS New_LastName,
		CAST(h.Address AS NVARCHAR(MAX)) AS Old_Address, 
		CAST(c.Address AS NVARCHAR(MAX)) AS New_Address
	FROM 
		dbo.Accounts FOR SYSTEM_TIME ALL AS c
	INNER JOIN 
		dbo.Accounts FOR SYSTEM_TIME ALL AS h 
		ON c.AccountId = h.AccountId
	WHERE 
		h.ValidTo = c.ValidFrom
),
UnpivotedOld AS (
	SELECT 
		AccountId,
		[From],
		[To],
		REPLACE(ColumnName, 'Old_', '') AS ColumnName,
		Value AS OldValue
	FROM 
		ChangedRows
	UNPIVOT (
		Value FOR ColumnName IN (Old_FirstName, Old_LastName, Old_Address)
	) AS up
),
UnpivotedNew AS (
	SELECT 
		AccountId,
		[From],
		[To],
		REPLACE(ColumnName, 'New_', '') AS ColumnName,
		Value AS NewValue
	FROM 
		ChangedRows
	UNPIVOT (
		Value FOR ColumnName IN (New_FirstName, New_LastName, New_Address)
	) AS up
)
SELECT 
	o.AccountId,
	o.ColumnName,
	o.OldValue,
	n.NewValue,
	o.[From],
	o.[To]
FROM 
	UnpivotedOld o
JOIN 
	UnpivotedNew n 
	ON o.AccountId = n.AccountId 
	AND o.ColumnName = n.ColumnName 
	AND o.[From] = n.[From] 
	AND o.[To] = n.[To]
WHERE 
	(o.OldValue IS NULL AND n.NewValue IS NOT NULL)
	OR (o.OldValue IS NOT NULL AND n.NewValue IS NULL)
	OR (o.OldValue <> n.NewValue)
ORDER BY 
	o.AccountId, 
	o.[From];

Integrating with Entity Framework Core

  • Define Entity Classes: Create entity classes that represent both the current and historical tables.
  • Configure Model: Use the IsTemporal() method inside OnModelCreating to configure the temporal nature of the tables.
modelBuilder.Entity<Account>()
	.ToTable("Accounts", tb => tb.IsTemporal());

EF Core supports queries that include historical data through several new query operators:

  • TemporalAsOf
  • TemporalAll
  • TemporalFromTo
  • TemporalBetween
  • TemporalContainedIn
var history = context
	.Accounts
	.TemporalAll()
	.Where(c => c.FirstName == "Mark")
	.OrderBy(c => EF.Property<DateTime>(c, "ValidFrom"))
	.Select(
		a => new
		{
			Account = a,
			ValidFrom = EF.Property<DateTime>(c, "ValidFrom"),
			ValidTo = EF.Property<DateTime>(c, "ValidTo")
		})
	.ToList();

Conclusion

SQL Server's temporal tables provide a robust framework for tracking and querying historical data changes. By understanding their structure and implementing them effectively, organizations can enhance data auditing, analysis, and recovery processes, ensuring data integrity and supporting informed decision-making.

While temporal tables offer powerful historical tracking, be mindful of the potential performance implications, especially with large datasets and frequent updates. Regular maintenance and indexing strategies are essential.

Historical data can grow significantly over time. Implementing data retention policies and archiving strategies may be necessary to manage storage requirements effectively.