Blog

Time under control: How temporal tables are revolutionizing your data management

Manage data in a completely new way

Blog

Time under control: How temporal tables are revolutionizing your data management

Manage data in a completely new way

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”.

BAYOOTEC_Temporal-Tables-Datenmanagement

What are the advantages of Temporal Tables?

Temporal tables offer several advantages over the manual storage of data in historized tables.

  • Historical data management: Temporal tables make it possible to keep a historical record of data changes in the database. This is useful for tracking the development of data over time and for looking back at past states.
  • Time-based queries: Temporal tables facilitate the execution of queries that refer to a specific point in time or a period in the past. This is particularly helpful if you want to perform trend-based analyses or compare historical data.
  • Data archiving: Historical data can be efficiently archived in Temporal Tables without compromising the integrity of current data. This enables better data management and adherence to compliance requirements.
  • Error analysis and compliance:Temporal tables are useful for tracking and monitoring data changes. This can help with troubleshooting, identifying data issues and adhering to compliance regulations such as data protection regulations.
  • Time-controlled audits: Temporal tables enable the simple creation of time stamps and audit logs for data changes. This makes it easier to track who made which changes to the data and when.
  • Restoring data:Temporal tables can help in the event of data loss or unwanted changes, as they offer the possibility of restoring the data status to an earlier point in time.
  • Easy data migration: If you need to migrate data between different versions of an application or database, Temporal Tables make the process easier as they already contain historical data.
  • Data warehouse integration: Temporal tables can be integrated into data warehouses to make historical data available for analytical purposes. This is particularly valuable for business intelligence and data analytics applications.
  • Ease of use: Querying historical data in Temporal Tables is usually done in an intuitive way, which improves user-friendliness.
  • Better data qualityTemporal tables make it possible to monitor data changes and identify sources of error, which can help to improve data quality.

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.

Temporal Tables Datenmanagement

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:

Temporal Tables Datenmanagement

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.

Temporal Tables Datenmanagement

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.

Temporal Tables Datenmanagement

Retrieve current data: If you want to retrieve the current data, you can do this in the same way without using the AsOf method.

Temporal Tables Datenmanagement

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.

BAYOOTEC -

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!

Share this blog
BAYOOTEC - Softwareentwicklung von Enterprise Software
About the author
BAYOOTEC Team - Beatrice Mende, Software Architect
Beatrice Mende, SENIOR SOFTWARE ARCHITECT

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.

  • An effective content management system is the centerpiece of your digital identity. But which CMS is the right one for me? Our whitepaper provides valuable insights, selection criteria and best practices to help you make an well-informed decision.

  • 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.

  • In 10 years of collaboration with Freudenberg Sealing Technologies and BAYOOTEC, many digital solutions have been realized around sales and around increasing product interest. FST has always attached importance to features of cooperation such as stability in the form of low team fluctuation, an understanding of the scope of solutions and a sense of responsibility.

  • As one of the best-known service providers for user experience, UID complements our portfolio. Where we look forward to exchanging experiences in many areas, such as expertise around digital transformation or UX/UI, the close collaboration is also a great benefit for you as a customer.

  • Is it possible for AI like GitHub Copilot to help us develop software in a more efficient way? Or will AI replace our jobs in the future?

  • Is it possible for AI like GitHub Copilot to help us engineer software in a more efficient way? Or will AI replace our jobs in the future?

BAYOOTEC - Softwareentwicklung von Enterprise Software