SQL Server Temporal Table Cleanup and Audit Use

By Tom Nonmacher

One of the most powerful features of SQL Server 2022 and Azure SQL is the ability to create temporal tables. A temporal table is an extension of a regular table that allows you to keep a history of data changes. However, like any powerful tool, temporal tables require proper management. As data accumulates over time, you will need to conduct regular cleanups to ensure optimal performance. In this blog post, we will explore how to clean up SQL Server temporal tables and how to use them for auditing purposes.

Before we delve into the cleanup process, it's important to understand how temporal tables work. When you create a temporal table, SQL Server automatically creates a history table that stores old versions of rows. Each row in the history table has two additional datetime2 columns, SysStartTime and SysEndTime, which indicate when the row was valid in the main table. 

-- Creating a temporal table

CREATE TABLE Orders

(

    OrderID int NOT NULL PRIMARY KEY CLUSTERED,

    ProductID int NOT NULL,

    Quantity int NOT NULL,

    SysStartTime datetime2 GENERATED ALWAYS AS ROW START,

    SysEndTime datetime2 GENERATED ALWAYS AS ROW END,

    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),

) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

Cleaning up a temporal table involves removing old rows from the history table. However, you cannot directly delete rows from a history table. Instead, you should turn off system versioning, delete old rows, and then turn system versioning back on. Here's an example: 

-- Cleaning up a temporal table

ALTER TABLE Orders SET (SYSTEM_VERSIONING = OFF);

DELETE FROM OrdersHistory WHERE SysEndTime < DATEADD(month, -6, GETDATE());

ALTER TABLE Orders SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

Temporal tables are not just for data recovery, but also for auditing purposes. They provide a history of data changes, which is crucial for audit trails. You can query a temporal table for a specific point in time or a range of time to see what the data looked like. This is incredibly useful for tracking changes and understanding the state of your data at any given moment. 

-- Querying a temporal table for a specific point in time

SELECT * FROM Orders FOR SYSTEM_TIME AS OF '2025-01-01' WHERE OrderID = 123;

In the era of big data, integrating technologies such as Microsoft Fabric, Delta Lake, OpenAI + SQL, and Databricks with your SQL Server can significantly enhance your data management and auditing capabilities. For instance, Delta Lake can help manage and organize vast amounts of historical data from your temporal tables, while Databricks and OpenAI can assist in analyzing and gaining insights from this data.

In conclusion, SQL Server temporal tables are a powerful tool for data recovery and auditing. However, they require careful management and regular cleanups to maintain optimal performance. By combining SQL Server with technologies like Microsoft Fabric, Delta Lake, OpenAI + SQL, and Databricks, you can take your data management and auditing capabilities to the next level.

Check out the latest articles from all our sites:




1ECA8E
Please enter the code from the image above in the box below.