DEV Community

loading...

SQL Server Temporal Tables

Joe Enos
Software developer/architect, mostly working with .NET and SQL Server.
・1 min read

If you need to create a history table to track all changes to a table in SQL Server, you can manually create another table and triggers, or you can use temporal tables.

When creating a table, you need to add a few extra things:

CREATE TABLE dbo.Games (
    GameID INT NOT NULL IDENTITY
    ,CONSTRAINT PK_Games PRIMARY KEY (GameID)

    ,Team1Score INT NOT NULL
    ,Team2Score INT NOT NULL
    ,GameDate DATETIME2 NOT NULL

    ,AddDate DATETIME2 NOT NULL
        CONSTRAINT DF_Games_AddDate DEFAULT (SYSUTCDATETIME())

    ,_StartDateTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
    ,_EndDateTime DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
    ,PERIOD FOR SYSTEM_TIME (_StartDateTime, _EndDateTime)
) WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.GamesHistory));
GO
Enter fullscreen mode Exit fullscreen mode

Note the _StartDateTime, _EndDateTime, PERIOD, and the SYSTEM_VERSIONING. Put all that together, and you've got versioning done.

You'll need a corresponding history table, with all the same columns but none of the special stuff:

CREATE TABLE dbo.GamesHistory (
    GameID INT NOT NULL
    ,Team1Score INT NOT NULL
    ,Team2Score INT NOT NULL
    ,GameDate DATETIME2 NOT NULL
    ,AddDate DATETIME2 NOT NULL
    ,_StartDateTime DATETIME2 NOT NULL
    ,_EndDateTime DATETIME2 NOT NULL
);
GO
Enter fullscreen mode Exit fullscreen mode

When you insert into the Games table, nothing special happens. But when you update or delete that record, a new record is automatically inserted in the history table, so you always can see exactly what happened and when.

Discussion (0)