Temporal Table was added in SQL SERVER 2016. The primary feature of temporal table is to record a table’s history data. Temporal table will record every data that is added, updated or deleted in a table in any movement. The new version of EntityFramework Core also has some Temporal Table functions. Temporal Table primary has these three functions.
Auditing: For certain industries, such as financial or pharmactural institutions, historical records can be tracked to see if any data has been added or deleted.
History Data Analysis: It is possible to analyze the historical records of a company’s products to determine sales performance, especially during specific time periods. This is also useful for analyzing stock market trends.
Data recovery: For example, in a bank, if something goes wrong due to misuse, data can be recovered by observing and tracking historical records.
Example:
Build a table
Build a table with its temporal table. Table schema is shown below。The table’s name is BookInventory. The table has itstemporal table as dbo.BookInventoryHistory。BookInventoryHistory is System-Versioned。”System-Versioned” means that SQL Server can automatically generate and maintain a Historical Table.
GENERATED ALWAYS AS ROW START: valid start date for the data row
GENERATED ALWAYS AS ROW END: end date for the data row
PERIOD FOR SYSTEM_TIME :indicate the valid start and end time
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.BookInventoryHistory):
Enabled activating Temporal Table, dbo.BookInventoryHistory is the temporal table of BookInventoryHistory.
Add Data
BookInventory Inserts two data(shown below)
dbo.BookInventory
dbo.BookInventoryHistory
We will oberserve BookInventory table and its temporal table(BookInventoryHistory) after these two data are added. BookInventory has these two data, but BookInventory hasn’t yet. This is because the first step is ‘Insert’, we have to wait the next step such as ‘Update’, the first inserted data will then be stored in BookInventoryHistory(Temporal table).
Update Data-Step 1
dbo.BookInventory
dbo.BookInventoryHistory
After updated these two data, the table BookInventory has these two modified data. Then we see BookInventoryHistory has these two unmodified data. SysStartTime is the date the data has originated generated, SysEndTime is the updated(modified) date.
Update Data – Step 2
dbo.BookInventory
dbo.BookInventoryHistory
We then check the outcome of Update data. This time we update two data. The temporal table BookInventoryHistory shall have four data(last updated data and also this updated data). We query the table BookInventoryHistory table, the result is as expected. BookInventoryHistory can track every updated data, as well as its modified time.
Delete Data
dbo.BookInventory
dbo.BookInventoryHistory
This time we delete one data form the table to see how the temporal table behaves. After deleting a data, BookInventory only has one data remained(another data is deleted). We then observe BookInventoryHistory and see there are five data stored its data(four updated data and one deleted data).
Track Data
Dbo.BookInventory
The valid active data at the time ‘2024-09-02 02:23:57.3447172’
Conclusion
Temporal Table has its constraints such as constraint is not available in temporal table. Temporal table also needs additional memory, and it is complicate to increase performance for browsing temporal table. However, it is very useful in tracking historical modified data. Prior Sql Server 2016, we have to use Trigger or Store Procedure to track Historical data. I will introduce how to use Temporal Table in EntityFramework Core.
References
Microsoft Docs, “Temporal Tables“
Pluralsight Couse Querying Data in EF Core– Temporal Tables
The importance of traceability of changes, https://itequia.com/en/table-versioning-in-sql-server-what-is-it-and-how-does-it-work/