Post
Ask Question

What are Temporal Tables in SQL Server?

What is a temporal table?

A temporal table is a type of database table that is designed to automatically track changes over time without requiring complex custom code or triggers. It does this by including system-generated columns representing each row's valid time period in the table. Also called System-Versioned Temporal Tables.

System-Versioned Temporal Tables

In this type of temporal table, the system automatically tracks changes to the data over time by creating a new version of each row every time it is updated or deleted. The original row is retained with its valid time period, and a new version is created with its own valid time period. This allows you to view the history of the table at any point in time and query it as if it were a snapshot of the data at that time.

Temporal Tables

How to create a temporal table?

Creating a temporal table in SQL Server involves creating a table with two additional datetime 2 columns to track the period of validity for each row: a start time column and an end time column.

CREATE TABLE dbo.MyTemporalTable (
   Id INT PRIMARY KEY,
   Name VARCHAR(50),
   EffectiveStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
   EffectiveEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME (EffectiveStartTime, EffectiveEndTime)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTemporalTable_History));

How to query the temporal table?

Temporal tables can be queries similar to database tables. A new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the main and history tables. This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

The five sub-clauses

  1. AS OF <DateTime>
  2. FROM <EffectiveStartTime> TO <EffectiveEndTime>
  3. BETWEEN <EffectiveStartTime> AND <EffectiveEndTime>
  4. CONTAINED IN (EffectiveStartTime, <EffectiveEndTime>)
  5. ALL
-- AS OF (EffectiveStartTime<= date_time AND EffectiveEndTime> date_time)
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME AS OF '2023-03-10 T10:00:00.0000000'

-- FROM (EffectiveStartTime< date_time AND EffectiveEndTime > date_time)
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME FROM '2022-01-01' TO '2022-12-31'

--BETWEEN (EffectiveStartTime<= date_time AND EffectiveEndTime > date_time)
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME BETWEEN '2022-01-01' AND '2022-12-31'

--CONTAINED (EffectiveStartTime>= date_time AND EffectiveEndTime <= date_time)
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME CONTAINED IN ('2023-01-01', '2023-02-28')

--ALL (All rows)
SELECT * FROM dbo.MyTemporalTable FOR SYSTEM_TIME ALL

How to drop the temporal table?

Dropping a temporal table is similar to dropping any other table in your database. However, we need to consider a few things before dropping a temporal table to avoid data loss and ensure that the cleanup process is handled correctly.

Here are the general steps to drop a temporal table.

Drop the dependent objects: Before dropping the temporal table, you should first drop any dependent objects, such as triggers, indexes, or foreign keys that reference the temporal table.

Disable temporal feature: Disable the temporal feature of the table by running the ALTER TABLE statement to remove the system-versioning or application-time period specification.

ALTER TABLE dbo.MyTemporalTable SET (SYSTEM_VERSIONING = OFF);

Drop the temporal table: After removing all dependent objects and disabling the temporal feature of the table, drop the table using the DROP TABLE statement.

DROP TABLE IF EXISTS dbo.MyTemporalTable;

Limitations of temporal table

  • Storage requirements: System-versioned temporal tables require additional storage to maintain historical versions of the data. The size of the history table can grow quickly, especially for tables with many updates or deletes, which can impact performance and increase storage costs.
  • Query performance: Querying a temporal table can be more complex than querying a regular table, especially if you need to retrieve data from both the current and historical versions of the data. Depending on the database system, query performance may be slower for temporal tables than for regular tables.
  • Configuration complexity: Setting up a temporal table requires some additional configuration steps compared to a regular table. This can make it more complex to create and maintain the table, especially if you are not familiar with the temporal table syntax.
  • Compatibility: Not all database systems support temporal tables. While SQL Server, Oracle, and PostgreSQL all support temporal tables, other systems may not. This can limit your ability to use temporal tables if you need to migrate to a different database system in the future.
  • Migration complexity: If you have an existing table that you want to make temporal, you may need to perform a data migration to move the existing data to the history table. This can be complex and time-consuming, especially for large tables.

Advantages of temporal table

The main advantage of a temporal table is that it allows you to maintain a full history of changes to your data over time. This provides several benefits, including:

  • Auditing: With a temporal table, you can easily track changes to your data over time. This is useful for auditing purposes, as you can see when and how data was modified, deleted, or added.
  • Compliance: Many industries have regulatory requirements that mandate data retention policies. By maintaining a full history of changes to your data, you can ensure compliance with these requirements.
  • Recovery: In the event of data loss or corruption, a temporal table can be used to recover lost data. You can restore the table to a previous point in time and recover data that was lost or corrupted.
  • Analysis: A temporal table can be used for trend analysis, as you can see how data has changed over time. This can be useful for identifying patterns, trends, and anomalies in your data.
  • Data versioning: With a temporal table, you can easily maintain multiple versions of your data. This is useful if you need to maintain different versions of a record for different users or if you need to create a new version of a record without overwriting the previous version.

Conclusion

Overall, the temporal table provides a powerful mechanism for maintaining a full history of changes to your data. This can be useful for a variety of purposes, including auditing, compliance, recovery, analysis, and data versioning. By understanding the limitations of temporal tables, you can make an informed decision about whether they are the right solution for your needs.


Similar Articles