Welcome Dev, if you’re interested in learning about temporal tables in SQL Server, you’re in the right place. This article will cover everything you need to know to start leveraging the power of temporal tables in your SQL Server database.
What Are Temporal Tables?
Temporal tables are a new feature introduced in SQL Server 2016 that allow you to track changes to your data over time. Unlike traditional tables that only store the current state of your data, temporal tables maintain a full history of all changes made to your data.
Temporal tables work by storing two copies of your data. The first copy is the current version of your data, stored in the main table. The second copy is a history of all changes made to your data, stored in a separate history table.
By maintaining this full history of changes, you can easily track how your data has changed over time, perform point-in-time analysis, and recover from data corruption or user errors.
Now that we understand what temporal tables are, let’s dive deeper into how they work and how you can use them in your own database.
How Do Temporal Tables Work?
As mentioned earlier, temporal tables store two copies of your data: the current data and a history of all changes. The history table is created automatically when you create a temporal table.
Column Name |
Data Type |
Description |
|---|---|---|
PeriodStart |
Datetime2 |
The starting date and time of the period. |
PeriodEnd |
Datetime2 |
The ending date and time of the period. |
Column1 |
DataType |
The first column of your table. |
Column2 |
DataType |
The second column of your table. |
Every time a change is made to your temporal table, a new row is added to the history table. The history table includes the start and end dates of the time period during which the row was valid. This is known as the system-versioned period.
The system-versioned period is determined by two columns in the history table: period start and period end. These columns are automatically maintained by SQL Server and cannot be modified directly by users.
When you query a temporal table, SQL Server automatically joins the main table and the history table together using the system-versioned period. This allows you to see the full history of your data, as well as the current state of your data.
How to Create Temporal Tables?
Creating a temporal table is simple. You can create a temporal table using T-SQL code or using SQL Server Management Studio.
To create a temporal table using T-SQL, you can use the following syntax:
CREATE TABLE TemporalTable
(
Column1 DataType,
Column2 DataType,
PeriodStart Datetime2(0) GENERATED ALWAYS AS ROW START,
PeriodEnd Datetime2(0) GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (PeriodStart, PeriodEnd)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = HistoryTable));
This code creates a temporal table named TemporalTable with two columns, Column1 and Column2, and two system-versioned columns, PeriodStart and PeriodEnd. The PERIOD FOR SYSTEM_TIME syntax specifies which columns are used to determine the time period for each row, and the WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = HistoryTable)) syntax enables system-versioning for the table and specifies the name of the history table.
To create a temporal table using SQL Server Management Studio:
- Open SQL Server Management Studio and connect to your database.
- Right-click on the Tables folder and select “New Temporal Table”.
- Enter the column names and data types for your table.
- Specify the system-versioned period by selecting the start and end columns from the dropdown lists.
- Click “OK” to create your temporal table.
How to Query Temporal Tables?
Querying a temporal table is similar to querying a traditional table. You can use standard SQL queries to retrieve the current state of your data, as well as the history of your data.
To retrieve the current state of your data, you can use a simple SELECT statement:
SELECT * FROM TemporalTable;
This query returns all rows from the current state of the TemporalTable. By default, SQL Server uses the current system time to determine which rows are valid at the time of the query.
To retrieve the history of your data, you can use the same SELECT statement with an additional FOR SYSTEM_TIME clause:
SELECT * FROM TemporalTable FOR SYSTEM_TIME ALL;
This query returns all rows from the history of the TemporalTable. The FOR SYSTEM_TIME ALL clause specifies that all rows from the history table should be included in the result set, not just the rows that are valid at the current system time.
How to Update Temporal Tables?
Updating a temporal table works the same way as updating a traditional table. You can use the UPDATE statement to modify the current state of your data.
When you update a row in a temporal table, SQL Server automatically creates a new row in the history table to record the previous state of the row. The system-versioned period for the previous row is set to end at the time of the update, and the system-versioned period for the new row is set to start at the time of the update.
For example, let’s say you have a temporal table with two columns, Column1 and Column2, and a single row with values “Value1” and “Value2”. If you execute the following update statement:
UPDATE TemporalTable SET Column2 = 'NewValue2' WHERE Column1 = 'Value1';
SQL Server will create a new row in the history table with values “Value1”, “Value2”, and an end date of the time of the update. The original row will be updated with the new value for Column2 and a new start date equal to the time of the update.
How to Delete Temporal Tables?
Deleting a temporal table works the same way as deleting a traditional table. You can use the DROP TABLE statement to delete a temporal table and its associated history table.
When you delete a temporal table, SQL Server automatically deletes the history table as well. This ensures that all data associated with the temporal table is removed from the database.
FAQs
How Do I Know If My Database Supports Temporal Tables?
Temporal tables were introduced in SQL Server 2016, so if you’re using a version of SQL Server prior to 2016, you won’t be able to use temporal tables. To check if your database supports temporal tables, you can run the following query:
SELECT compatibility_level FROM sys.databases where name = 'yourdatabase';
If the compatibility level is 130 or higher, your database supports temporal tables.
Do Temporal Tables Require Additional Storage Space?
Yes, temporal tables require additional storage space to maintain the history of your data. The amount of storage required will depend on the size of your database and the frequency of changes to your data.
Can I Create a Temporal Table on an Existing Table?
Yes, you can create a temporal table on an existing table using the ALTER TABLE statement:
ALTER TABLE existing_table ADD PERIOD FOR SYSTEM_TIME (PeriodStart, PeriodEnd) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = HistoryTable));
This code adds a system-versioned period to an existing table and enables system-versioning on the table. The name of the history table must be specified.
Can I Use Temporal Tables with Partitioning?
Yes, temporal tables can be used with table partitioning. You can partition both the main table and the history table independently.
Can I Query a Temporal Table Using a Specific Point in Time?
Yes, you can use the AS OF clause to query a temporal table at a specific point in time:
SELECT * FROM TemporalTable FOR SYSTEM_TIME AS OF '2021-01-01 00:00:00';
This query returns all rows from the temporal table that were valid at the specified point in time.
Can I Use Temporal Tables with In-Memory OLTP?
Yes, temporal tables can be used with In-Memory OLTP. However, there are some restrictions on the use of temporal tables with In-Memory OLTP. For more information, see the Microsoft documentation on temporal tables.
Conclusion
Temporal tables are a powerful new feature in SQL Server that allow you to track changes to your data over time. By maintaining a full history of changes, temporal tables enable you to perform point-in-time analysis, track data changes, and recover from data corruption or user errors. With the information provided in this article, you should be able to create, query, and manage temporal tables in your own SQL Server database.