Are you looking for an effective method to revolutionize your data management and also implement methods for a history and an audit? Then you should definitely get to grips with the topic of temporal tables.
With these special settings for tables in Microsoft SQL servers, you have the opportunity to manage data in a completely new way. By creating a “system-period temporal table”, you can not only save the current data in a table, but also track the changes over time. This means you always have an overview of the development of your data and have access to a comprehensive history. Try it out and start creating your first temporal table now.
What are temporal tables?
You’ve probably heard of database tables before. Temporal (not to be confused with temporary) tables are a special type of table that allow data to be stored not only currently, but also to record its historical changes.
A temporal table also contains a system period start date and end date for each line, which indicates the period for which this line was valid. This means that not only the current data can be retrieved, but also past states of the stored information.
There are two types of temporal tables: system-versioned and application-time-period. With system-versioned temporal tables, the history is automatically managed by creating versions of the data records and saved in a separate history table. The advantage is that you don’t have to write any complicated code for this – the system does it automatically. An example of this is the implementation in Microsoft SQL Server from 2016 with the command “CREATE TABLE foo (….) SYSTEM VERSIONING”.
What are the advantages of Temporal Tables?
Temporal tables offer several advantages over the manual storage of data in historized tables.
How do Temporal Tables work at database level in MS SQL?
To create a temporal table in MS SQL Server, you must create a normal table and link it to a history table. The main table contains the current data, while the history table stores the historical versions of the data. Only the changed data records are saved and not the complete status of the table.
If you change data in the main table, SQL Server automatically creates a historical version of the changed data in the history table. This is done using timestamps (ValidFrom and ValidTo), which specify the validity period of the data records. This allows you to track data changes.
To query historical data, you can use the “FOR SYSTEM_TIME” clause in your SQL queries. Here is a simple example:
This clause allows you to retrieve data at a specific point in time or within a period in the past.
With “OLD TABLE … SET (SYSTEM_VERSIONING = OFF)” you can deactivate the system versioning for a temporal table. However, this does not automatically delete the historical data in the history table. If you reactivate system versioning, you can continue to use the historical data.
How do Temporal Tables work with LINQ in EF Core 6?
To first create a model for the temporal table, you need a model in your EF Core application that represents the temporal table. This model should contain the relevant properties for the main table.
You can retrieve historical data using LINQ queries by using the AsOf method. This method allows you to specify the time or period for which you want to retrieve historical data.
Retrieve current data: If you want to retrieve the current data, you can do this in the same way without using the AsOf method.
To use it, make sure that you have activated system versioning for the temporal table in the database. You can do this via SQL Server Management Studio (database-first approach) or via a migration to EF Core (code-first approach).
Our experience
Temporal tables are increasingly being used by us, as our customers are also convinced of the benefits. They are particularly relevant for customers for whom we implement and support business intelligence applications, as budget planning and sales changes need to be analyzed.
With the introduction of Temporal Tables, a data record can now simply be selected at time X and compared with the current status. In the past, “snapshots” of the data had to be saved separately. This resulted in higher memory usage and poorer performance.
We have also already benefited from Temporal Tables when importing data records into one of our systems, as it is not always possible to make a backup in the event of a faulty import. However, historical queries can be used to restore erroneously deleted data records at any time without deleting changes to other data records.
Overall, the use of Temporal Tables has become indispensable in many of our projects and has already found its way into everyday project work thanks to its low implementation costs and great benefits.
Conclusion: Temporal tables are revolutionizing data management
You now know what temporal tables are and how they can revolutionize your data management. If you create a temporal table, you can track all changes to your data over time. This means that you can always retrieve the current version of your data, but you can also access previous versions to see how your data has developed over time. With a system period or a system-versioned schema in MS SQL Server, for example, you can set up and manage everything easily. In addition to the current row data, such a table also saves a time period (start date/end date) for each row as well as automatically generated history data for each change to the relevant row.
This enables companies and developers alike to flexibly design time-based queries and analyses and thus gain more insights from their data. In addition, the system-versioned schema of Microsoft SQL Server from version 2016 offers a simple option for migrating existing tables to system-versioned tables.
The use of temporal tables can therefore improve the entire data life cycle management – from data storage to processing and analysis. And since .NET EF Core 6 has simplified integration, they are even easier to use.
So what are you waiting for? Start revolutionizing your data management with Temporal Tables now!
We have valued Bea as part of our BAYOOTEC team for almost 9 years. Her journey began 2.5 years ago as a software developer and she now supports us with her expertise as a senior software architect.
Bea is not only professionally successful, but also a passionate animal lover with a lively Shiba Inu and two snakes at home. She also devotes her free time to board games, Dungeons & Dragons and various online games. Her artistic streak is evident in her choral singing and costume tailoring. Her versatility also manifests itself in her reputation for always finding creative solutions to problems.
In professional projects, Bea’s area of responsibility is .NET, with a particular interest in SQL databases. With her technical expertise and her ability to creatively overcome challenges, Bea has played a key role in shaping BAYOOTEC’s success story.
