Here we have an application that shares district and date wise updated current temperature, when temperature of any district with current date changes, we update that with current temperature. In our old system we only keep the district and date wise a new row but now we need total periodic change history for analysis purposes.
Suppose we want to see today’s periodic temperature ups-down history of Dhaka district; how can we implement that.
Here comes the System-Versioned Temporal Table. It is a type of user table designed to keep a full history of data changes, allowing easy point-in-time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system.
System-versioned temporal tables were introduced in SQL Server 2016, and they provide a way to track changes to data over time. With a temporal table, SQL Server automatically creates a history table that stores all of the changes made to the main table, including when the changes were made and who made them.
Creating a system-versioned temporal table in SQL Server is similar to creating a regular table, but with a few additional steps.
First, you need to enable system versioning for the table by specifying the PERIOD FOR SYSTEM_TIME clause in the table definition. This clause defines the name of the system-versioning period column, which is used to track when changes were made to the data.
Here’s an example of how to create a system-versioned temporal table:
1. Create a temporal table with an anonymous history table
3. Or create a temporal table with a user-defined history table
4. Altering an Existing Table to Support Temporal Data
Now It’s time to update data to see the change history, We will consistently update the temperature of Dhaka for the date of 2023-05-09 from our application.
Now we are able to see the change in history for a day. For example — From ‘2023-05-09 08:54:23.2678482’ to ‘2023-05-09 08:56:13.4474165’ the temperature of Dhaka was 32 C and last updated/current temperature is 38 C.
This is how we can track the changes and use that history for further analysis and reporting.
