SQL Server/Azure SQL temporal tables

Article
02/18/2023

SQL Server temporal tables automatically keep track of all data ever stored in a table, even after that data has been updated or deleted. This is achieved by creating a parallel "history table" into which timestamped historical data is stored whenever a change is made to the main table. This allows historical data to be queried, such as for auditing, or restored, such as for recovery after accidental mutation or deletion.

EF Core supports:

The creation of temporal tables using EF Core Migrations
Transformation of existing tables into temporal tables, again using Migrations
Querying historical data
Restoring data from some point in the past

Configuring a temporal table

The model builder can be used to configure a table as temporal. For example:

modelBuilder
    .Entity<Employee>()
    .ToTable("Employees", b => b.IsTemporal());

Tip

The code shown here comes from TemporalTablesSample.cs.

When using EF Core to create the database, the new table is configured as a temporal table with the SQL Server defaults for the timestamps and history table. For example, consider an Employee entity type:

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public string Department { get; set; }
    public string Address { get; set; }
    public decimal AnnualSalary { get; set; }
}

The temporal table created looks like this:

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Employees] (
    [EmployeeId] uniqueidentifier NOT NULL,
    [Name] nvarchar(100) NULL,
    [Position] nvarchar(100) NULL,
    [Department] nvarchar(100) NULL,
    [Address] nvarchar(1024) NULL,
    [AnnualSalary] decimal(10,2) NOT NULL,
    [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeId]),
    PERIOD FOR SYSTEM_TIME([PeriodStart], [PeriodEnd])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[EmployeeHistory]))');

Notice that SQL Server creates two hidden datetime2 columns called PeriodEnd and PeriodStart. These "period columns" represent the time range during which the data in the row existed. These columns are mapped to shadow properties in the EF Core model, allowing them to be used in queries as shown later.

Important

The times in these columns are always UTC time generated by SQL Server. UTC times are used for all operations involving temporal tables, such as in the queries shown below.

Notice also that an associated history table called EmployeeHistory is created automatically. The names of the period columns and history table can be changed with additional configuration to the model builder. For example:

modelBuilder
    .Entity<Employee>()
    .ToTable(
        "Employees",
        b => b.IsTemporal(
            b =>
            {
                b.HasPeriodStart("ValidFrom");
                b.HasPeriodEnd("ValidTo");
                b.UseHistoryTable("EmployeeHistoricalData");
            }));

This is reflected in the table created by SQL Server:

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Employees] (
    [EmployeeId] uniqueidentifier NOT NULL,
    [Name] nvarchar(100) NULL,
    [Position] nvarchar(100) NULL,
    [Department] nvarchar(100) NULL,
    [Address] nvarchar(1024) NULL,
    [AnnualSalary] decimal(10,2) NOT NULL,
    [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeId]),
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[EmployeeHistoricalData]))');

Using temporal tables

Most of the time, temporal tables are used just like any other table. That is, the period columns and historical data are handled transparently by SQL Server such that the application can ignore them. For example, new entities can be saved to the database in the normal way:

context.AddRange(
    new Employee
    {
        Name = "Pinky Pie",
        Address = "Sugarcube Corner, Ponyville, Equestria",
        Department = "DevDiv",
        Position = "Party Organizer",
        AnnualSalary = 100.0m
    },
    new Employee
    {
        Name = "Rainbow Dash",
        Address = "Cloudominium, Ponyville, Equestria",
        Department = "DevDiv",
        Position = "Ponyville weather patrol",
        AnnualSalary = 900.0m
    },
    new Employee
    {
        Name = "Fluttershy",
        Address = "Everfree Forest, Equestria",
        Department = "DevDiv",
        Position = "Animal caretaker",
        AnnualSalary = 30.0m
    });

context.SaveChanges();

This data can then be queried, updated, and deleted in the normal way. For example:

var employee = context.Employees.Single(e => e.Name == "Rainbow Dash");
context.Remove(employee);
context.SaveChanges();

Also, after a normal tracking query, the values from the period columns of the current data can be accessed from the tracked entities. For example:

var employees = context.Employees.ToList();
foreach (var employee in employees)
{
    var employeeEntry = context.Entry(employee);
    var validFrom = employeeEntry.Property<DateTime>("ValidFrom").CurrentValue;
    var validTo = employeeEntry.Property<DateTime>("ValidTo").CurrentValue;

    Console.WriteLine($"  Employee {employee.Name} valid from {validFrom} to {validTo}");
}

This prints:

Starting data:
  Employee Pinky Pie valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM
  Employee Rainbow Dash valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM
  Employee Fluttershy valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM

Notice that the ValidTo column (by default called PeriodEnd) contains the datetime2 max value. This is always the case for the current rows in the table. The ValidFrom columns (by default called PeriodStart) contains the UTC time that the row was inserted.

Querying historical data

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

TemporalAsOf: Returns rows that were active (current) at the given UTC time. This is a single row from the current table or history table for a given primary key.
TemporalAll: Returns all rows in the historical data. This is typically many rows from the history table and/or the current table for a given primary key.
TemporalFromTo: Returns all rows that were active between two given UTC times. This may be many rows from the history table and/or the current table for a given primary key.
TemporalBetween: The same as TemporalFromTo, except that rows are included that became active on the upper boundary.
TemporalContainedIn: Returns all rows that started being active and ended being active between two given UTC times. This may be many rows from the history table and/or the current table for a given primary key.

Note

See the SQL Server temporal tables documentation for more information on exactly which rows are included for each of these operators.

For example, after making some updates and deletes to our data, we can run a query using TemporalAll to see the historical data:

var history = context
    .Employees
    .TemporalAll()
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => EF.Property<DateTime>(e, "ValidFrom"))
    .Select(
        e => new
        {
            Employee = e,
            ValidFrom = EF.Property<DateTime>(e, "ValidFrom"),
            ValidTo = EF.Property<DateTime>(e, "ValidTo")
        })
    .ToList();

foreach (var pointInTime in history)
{
    Console.WriteLine(
        $"  Employee {pointInTime.Employee.Name} was '{pointInTime.Employee.Position}' from {pointInTime.ValidFrom} to {pointInTime.ValidTo}");
}

Notice how the EF.Property method can be used to access values from the period columns. This is used in the OrderBy clause to sort the data, and then in a projection to include these values in the returned data.

This query brings back the following data:

Historical data for Rainbow Dash:
  Employee Rainbow Dash was 'Ponyville weather patrol' from 8/26/2021 4:38:58 PM to 8/26/2021 4:40:29 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM
  Employee Rainbow Dash was 'Wonderbolt' from 8/26/2021 4:43:29 PM to 8/26/2021 4:44:59 PM

Notice that the last row returned stopped being active at 8/26/2021 4:44:59 PM. This is because the row for Rainbow Dash was deleted from the main table at that time. We will see later how this data can be restored.

Similar queries can be written using TemporalFromTo, TemporalBetween, or TemporalContainedIn. For example:

var history = context
    .Employees
    .TemporalBetween(timeStamp2, timeStamp3)
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => EF.Property<DateTime>(e, "ValidFrom"))
    .Select(
        e => new
        {
            Employee = e,
            ValidFrom = EF.Property<DateTime>(e, "ValidFrom"),
            ValidTo = EF.Property<DateTime>(e, "ValidTo")
        })
    .ToList();

This query returns the following rows:

Historical data for Rainbow Dash between 8/26/2021 4:41:14 PM and 8/26/2021 4:42:44 PM:
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM

Restoring historical data

As mentioned above, Rainbow Dash was deleted from the Employees table. This was clearly a mistake, so let's go back to a point-in-time and restore the missing row from that time.

var employee = context
    .Employees
    .TemporalAsOf(timeStamp2)
    .Single(e => e.Name == "Rainbow Dash");

context.Add(employee);
context.SaveChanges();

This query returns a single row for Rainbow Dash as it was at the given UTC time. All queries using temporal operators are no-tracking by default, so the returned entity here is not tracked. This makes sense, because it does not currently exist in the main table. To re-insert the entity into the main table, we simply mark it as Added and then call SaveChanges.

After re-inserting the row Rainbow Dash, querying the historical data shows that the row was restored as it was at the given UTC time:

Historical data for Rainbow Dash:
  Employee Rainbow Dash was 'Ponyville weather patrol' from 8/26/2021 4:38:58 PM to 8/26/2021 4:40:29 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM
  Employee Rainbow Dash was 'Wonderbolt' from 8/26/2021 4:43:29 PM to 8/26/2021 4:44:59 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:44:59 PM to 12/31/9999 11:59:59 PM

Share via

SQL Server/Azure SQL temporal tables

Configuring a temporal table

Using temporal tables

Querying historical data

Restoring historical data

Additional resources