Post
Ask Question

Temporal Tables In SQL Server

What is Temporal Table?

A system-versioned temporal table is a type of table in SQL Server that automatically tracks the historical changes to the data in the table, by keeping a separate history table that stores all the previous versions of the data. This allows you to easily see how the data has changed over time and also enables you to perform temporal queries to retrieve the data as it existed at a specific point in time. The system-versioning is done by using two columns, one for the start date and one for the end date, that keep track of when the data was valid. The system manages this automatically and transparently to the user.

You can also create a temporal table by specifying the Transact-SQL statements directly, as shown in the example below. Note that the mandatory elements of every temporal table are the PERIOD definition and the SYSTEM_VERSIONING clause with a reference to another user table that will store historical row versions:

    CREATE TABLE [dbo].[AppUsers]
    (
        [UserID] int NOT NULL PRIMARY KEY CLUSTERED
      , [UserName] nvarchar(100) NOT NULL
      , [PagesVisited] int NOT NULL
      , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN
      , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END HIDDEN
      , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
     )
     WITH
     (
        SYSTEM_VERSIONING = ON
        (
            HISTORY_TABLE = dbo.AppUsersArchive,
            HISTORY_RETENTION_PERIOD = 2 MONTHS
        )
     );

ALTER TABLE AppUsers
    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 AppUsers
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AppUsersArchive));

Advantage of Temporal Table

The main advantage of using a system-versioned temporal table is the ability to easily track and analyze changes to the data over time. Some specific benefits include:

  • Auditing: Temporal tables can be used to track changes to the data, making it easier to identify who made a change and when.
  • Point-in-time reporting: You can retrieve the state of the data at a specific point in time, which is useful for generating historical reports or analyzing trends over time.
  • Data recovery: If data is accidentally deleted or modified, you can easily recover the previous version of the data.
  • Improved data integrity: By keeping a separate history table, temporal tables can help ensure that historical data is not accidentally modified or deleted.
  • Improved performance: By keeping a separate history table, temporal tables can help improve query performance for certain types of queries that retrieve historical data.

It's worth noting that temporal tables are only available in SQL Server 2016 and later.

Temporal Table Considerations and Limitations

There are some considerations and limitations to be aware of when working with temporal tables, due to the nature of system-versioning:

  • A temporal table must have a primary key defined in order to correlate records between the current table and the history table, and the history table can't have a primary key defined.
  • The SYSTEM_TIME period columns used to record the ValidFrom and ValidTo values must be defined with a datatype of datetime2.
  • By default, the history table is PAGE compressed.
  • While temporal tables support blob data types, such as (n)varchar(max), varbinary(max), (n)text, and image, they'll incur significant storage costs and have performance implications due to their size. As such, when designing your system, care should be taken when using these data types.
  • History table must be created in the same database as the current table. Temporal querying over linked servers isn't supported.
  • History table can't have constraints (primary key, foreign key, table or column constraints).
  • TRUNCATE TABLE isn't supported while SYSTEM_VERSIONING is ON.
  • Direct modification of the data in a history table isn't permitted.
  • INSTEAD OF triggers aren't permitted on either the current or the history table to avoid invalidating the DML logic. AFTER triggers are permitted only on the current table. 
  • They're blocked on the history table to avoid invalidating the DML logic.

Conclusion

Hope the article would have helped you in understanding Temporal tables.


Similar Articles