Hey there, Dev! Are you struggling with managing and keeping track of data changes in your SQL Server database? If so, then you’ve come to the right place!
In this article, we’ll dive into the world of SQL Server temporal tables, a powerful feature that allows you to store historical data and easily query previous states of your data.
What are SQL Server Temporal Tables?
SQL Server temporal tables are a new feature introduced in SQL Server 2016 that allow you to keep track of changes to data over time. They are essentially regular tables that have an added history table that tracks all changes made to the data.
With temporal tables, you can easily query historical data using a simple syntax, without having to write complex queries or storing multiple copies of your data.
How Do Temporal Tables Work?
Temporal tables work by storing a history table alongside the main table. Every time a row is inserted, deleted, or updated in the main table, a copy of the old row is stored in the history table along with a timestamp indicating when the change occurred.
This allows you to query the history table to retrieve previous states of your data, as well as query the current state of your data from the main table.
Why Use Temporal Tables?
Temporal tables provide several benefits over traditional methods of storing historical data:
- Easy to query historical data without having to write complex queries
- Ability to easily track changes to data over time
- Reduced storage requirements as you don’t need to store multiple copies of your data
- Auditing and compliance benefits as you can easily see who made changes to data and when
Limitations of Temporal Tables
While temporal tables are a powerful feature, there are some limitations to keep in mind:
- Only available in SQL Server 2016 and later
- Cannot be used with clustered columnstore indexes
- Cannot be used with full-text indexes
- Cannot be used with memory-optimized tables
Creating Temporal Tables
Creating a temporal table in SQL Server is a simple process. To create a temporal table, you first need to create the main table, and then create a history table that is linked to the main table.
Creating the Main Table
Creating the main table is the same as creating any other table in SQL Server. Here’s an example:
Column Name |
Data Type |
|---|---|
id |
INT |
name |
VARCHAR(50) |
email |
VARCHAR(50) |
start_date |
DATETIME2 |
end_date |
DATETIME2 |
salary |
DECIMAL(10,2) |
In this example, we’ve created a simple table to track employee data. We have columns for employee ID, name, email, start and end dates, and salary.
Creating the History Table
After creating the main table, we need to create a history table that is linked to the main table. Here’s an example:
Column Name |
Data Type |
|---|---|
id |
INT |
name |
VARCHAR(50) |
email |
VARCHAR(50) |
start_date |
DATETIME2 |
end_date |
DATETIME2 |
salary |
DECIMAL(10,2) |
start_time |
DATETIME2(3) |
end_time |
DATETIME2(3) |
operation |
NVARCHAR(10) |
In this example, we’ve created a history table that has the same columns as the main table, as well as three additional columns: start_time, end_time, and operation.
The start_time and end_time columns track when the row was inserted into the history table, and the operation column tracks what type of operation was performed (insert, update, or delete).
Linking the History Table to the Main Table
After creating both tables, we need to link the history table to the main table. This is done using a system-versioned temporal table. Here’s an example:
CREATE TABLE employee_temporal (id INT NOT NULL PRIMARY KEY CLUSTERED,name VARCHAR(50) NOT NULL,email VARCHAR(50) NOT NULL,start_date DATETIME2 NOT NULL,end_date DATETIME2 NOT NULL,salary DECIMAL(10,2) NOT NULL,start_time DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN,end_time DATETIME2(3) GENERATED ALWAYS AS ROW END HIDDEN,PERIOD FOR SYSTEM_TIME (start_time, end_time))WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.employee_history));In this example, we’ve used the CREATE TABLE statement to create a system-versioned temporal table named employee_temporal. The PERIOD FOR SYSTEM_TIME clause specifies the start_time and end_time columns as the period columns for the temporal table.
The WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.employee_history)) clause enables system-versioning for the table and specifies the history table to use (employee_history).
Working with Temporal Tables
Working with temporal tables is similar to working with regular tables. You can use standard SQL syntax to insert, update, delete, and query data.
Inserting Data
To insert data into a temporal table, you can use the INSERT statement as you would with a regular table. Here’s an example:
INSERT INTO employee_temporal (id, name, email, start_date, end_date, salary)VALUES (1, 'John Doe', 'jdoe@email.com', '2022-01-01', '2022-12-31', 50000.00);This will insert a new row into the main table and a copy of the old row into the history table with the start_time and end_time columns set to the current time.
Updating Data
To update data in a temporal table, you can use the UPDATE statement as you would with a regular table. Here’s an example:
UPDATE employee_temporalSET salary = 55000.00WHERE id = 1;This will update the salary column in the main table and insert a copy of the old row into the history table with the end_time column set to the current time and a new row in the main table with the start_time column set to the current time.
Deleting Data
To delete data from a temporal table, you can use the DELETE statement as you would with a regular table. Here’s an example:
DELETE FROM employee_temporalWHERE id = 1;This will delete the row from the main table and insert a copy of the old row into the history table with the end_time column set to the current time.
Querying Data
Querying data from a temporal table is where the real power of temporal tables comes into play. You can easily query historical data using a simple syntax. Here are some examples:
SELECT * FROM employee_temporal; -- Returns the current state of the dataSELECT * FROM employee_temporal FOR SYSTEM_TIME ALL; -- Returns all rows, including historical dataSELECT * FROM employee_temporal WHERE id = 1; -- Returns the current state of the row with id = 1SELECT * FROM employee_temporal FOR SYSTEM_TIME BETWEEN '2022-01-01' AND '2022-12-31'; -- Returns all rows that were valid during the given time rangeFAQ
What is the difference between a system-versioned temporal table and an application-time temporal table?
A system-versioned temporal table uses the system’s clock to track changes to data, while an application-time temporal table allows you to specify your own time column to track changes to data.
Can I use temporal tables with Azure SQL Database?
Yes, temporal tables are supported in Azure SQL Database.
Can I query historical data without using the FOR SYSTEM_TIME clause?
No, you must use the FOR SYSTEM_TIME clause to query historical data.
Can I use temporal tables with SQL Server Management Studio?
Yes, you can use SQL Server Management Studio to create and manage temporal tables.
Can I disable system versioning for a temporal table?
Yes, you can disable system versioning for a temporal table using the ALTER TABLE statement.
Conclusion
SQL Server temporal tables are a powerful feature that allows you to store historical data and easily query previous states of your data. By creating a history table alongside your main table, you can easily track changes to your data over time without having to store multiple copies of your data or write complex queries.
While there are some limitations to using temporal tables, they provide many benefits over traditional methods of storing historical data and can be a valuable tool for any developer working with SQL Server databases.