Time Travel in SQL Server: Using Temporal Tables for Historical Data Analysis

Posted on by Ian

Temporal tables, introduced in SQL Server 2016, provide a powerful mechanism for tracking historical changes to data. This feature is particularly useful for auditing purposes, allowing organisations to maintain a complete history of data modifications without the need for complex triggers or custom logging solutions.

In this article, we’ll explore how to implement and use temporal tables for auditing in SQL Server, along with examples to demonstrate.

What are Temporal Tables?

Temporal tables, also known as system-versioned temporal tables, are tables that automatically maintain a history of all data changes. They consist of two parts:

  • The current table, which holds the current state of the data.
  • A history table, which maintains all previous versions of the data.

Creating a Temporal Table

Let’s create a simple product inventory table and insert some initial data:

-- Create the product inventory table
CREATE TABLE ProductInventory (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Category NVARCHAR(50),
    Quantity INT,
    UnitPrice DECIMAL(10, 2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductInventoryHistory));

-- Insert some initial data
INSERT INTO ProductInventory (ProductID, ProductName, Category, Quantity, UnitPrice)
VALUES 
(1, 'Lightsaber', 'Weapons', 50, 299.99),
(2, 'Sonic Screwdriver', 'Tools', 30, 59.99),
(3, 'Tricorder', 'Electronics', 20, 199.99);

In this example, we created a ProductInventory table with system-versioning enabled. The ValidFrom and ValidTo columns are automatically managed by SQL Server to track the period for which each row version is valid.

Change Some Data

Now, let’s make some changes to the data and see how temporal tables track these modifications:

-- Update a product's quantity and price
UPDATE ProductInventory
SET Quantity = 45, UnitPrice = 319.99
WHERE ProductID = 1;

-- Change a product's category
UPDATE ProductInventory
SET Category = 'Gadgets'
WHERE ProductID = 2;

-- Delete a product
DELETE FROM ProductInventory
WHERE ProductID = 3;

-- Insert a new product
INSERT INTO ProductInventory (ProductID, ProductName, Category, Quantity, UnitPrice)
VALUES (4, 'Hoverboard', 'Transportation', 15, 499.99);

-- Update the price of another product
UPDATE ProductInventory
SET UnitPrice = 69.99
WHERE ProductID = 2;

I ran each of the above statements separately, with a pause in between in order to make some of the upcoming examples return more realistic results. You’ll see what I mean.

Querying Historical Data

To retrieve the historical data, we can use the FOR SYSTEM_TIME clause. Below are some example of queries we might use against a temporal table.

First, let’s view the current state of the table:

SELECT * FROM ProductInventory;

Result:

ProductID  ProductName        Category        Quantity  UnitPrice  ValidFrom                 ValidTo                 
---------  -----------------  --------------  --------  ---------  ------------------------  ------------------------
1          Lightsaber         Weapons         45        319.99     2024-07-27T03:29:54.852Z  9999-12-31T23:59:59.999Z
2          Sonic Screwdriver  Gadgets         30        69.99      2024-07-27T03:33:43.148Z  9999-12-31T23:59:59.999Z
4          Hoverboard         Transportation  15        499.99     2024-07-27T03:32:39.415Z  9999-12-31T23:59:59.999Z

That’s the current state of the table (i.e. after all the above updates had been done).

View all historical changes:

SELECT * FROM ProductInventory
FOR SYSTEM_TIME ALL
ORDER BY ProductID, ValidFrom;

Result:

ProductID  ProductName        Category        Quantity  UnitPrice  ValidFrom                 ValidTo                 
---------  -----------------  --------------  --------  ---------  ------------------------  ------------------------
1          Lightsaber         Weapons         50        299.99     2024-07-27T03:29:43.997Z  2024-07-27T03:29:54.852Z
1          Lightsaber         Weapons         45        319.99     2024-07-27T03:29:54.852Z  9999-12-31T23:59:59.999Z
2          Sonic Screwdriver  Tools           30        59.99      2024-07-27T03:29:43.997Z  2024-07-27T03:30:45.634Z
2          Sonic Screwdriver  Gadgets         30        59.99      2024-07-27T03:30:45.634Z  2024-07-27T03:33:43.148Z
2          Sonic Screwdriver  Gadgets         30        69.99      2024-07-27T03:33:43.148Z  9999-12-31T23:59:59.999Z
3          Tricorder          Electronics     20        199.99     2024-07-27T03:29:43.997Z  2024-07-27T03:32:17.802Z
4          Hoverboard         Transportation  15        499.99     2024-07-27T03:32:39.415Z  9999-12-31T23:59:59.999Z

As mentioned, I intentionally paused for a bit when I updated the data in the previous example. We can see the result of those pauses in the time stamps.

View the state of the table at a specific point in time:

DECLARE @PointInTime DATETIME2 = DATEADD(MINUTE, -5, GETUTCDATE());
SELECT * FROM ProductInventory
FOR SYSTEM_TIME AS OF @PointInTime;

Result:

ProductID  ProductName        Category     Quantity  UnitPrice  ValidFrom                 ValidTo                 
---------  -----------------  -----------  --------  ---------  ------------------------  ------------------------
1          Lightsaber         Weapons      45        319.99     2024-07-27T03:29:54.852Z  9999-12-31T23:59:59.999Z
3          Tricorder          Electronics  20        199.99     2024-07-27T03:29:43.997Z  2024-07-27T03:32:17.802Z
2          Sonic Screwdriver  Gadgets      30        59.99      2024-07-27T03:30:45.634Z  2024-07-27T03:33:43.148Z

Again, pausing between my UPDATE examples allowed these results to differ (slightly) from the results we got when we ran SELECT * FROM ProductInventory to get all current data. This query shows us what results we would have got if we’d run SELECT * FROM ProductInventory at the specified point in time. It’s like going back in time and checking the table’s contents at that time.

View changes for a specific product:

SELECT * FROM ProductInventory
FOR SYSTEM_TIME ALL
WHERE ProductID = 1
ORDER BY ValidFrom;

Result:

ProductID  ProductName  Category  Quantity  UnitPrice  ValidFrom                 ValidTo                 
---------  -----------  --------  --------  ---------  ------------------------  ------------------------
1          Lightsaber   Weapons   50        299.99     2024-07-27T03:29:43.997Z  2024-07-27T03:29:54.852Z
1          Lightsaber   Weapons   45        319.99     2024-07-27T03:29:54.852Z  9999-12-31T23:59:59.999Z

Analysing Changes

Temporal tables make it easy to analyse changes over time. Here’s an example of how to find all price changes:

SELECT 
    p1.ProductID,
    p1.ProductName,
    p1.UnitPrice AS OldPrice,
    p2.UnitPrice AS NewPrice,
    p1.ValidFrom AS ChangeDate
FROM 
    ProductInventory FOR SYSTEM_TIME ALL p1
    INNER JOIN ProductInventory FOR SYSTEM_TIME ALL p2
    ON p1.ProductID = p2.ProductID
    AND p1.ValidTo = p2.ValidFrom
WHERE 
    p1.UnitPrice <> p2.UnitPrice
ORDER BY 
    p1.ProductID, p1.ValidFrom;

Result:

ProductID  ProductName        OldPrice  NewPrice  ChangeDate              
---------  -----------------  --------  --------  ------------------------
1          Lightsaber         299.99    319.99    2024-07-27T03:29:43.997Z
2          Sonic Screwdriver  59.99     69.99     2024-07-27T03:30:45.634Z

This query compares consecutive versions of product records to identify price changes.

Conclusion

Temporal tables offer a robust, built-in solution for auditing data changes in SQL Server. By automatically maintaining a history of all modifications, they simplify the process of tracking and analysing data changes over time. Whether you’re tracking inventory fluctuations, meeting compliance requirements, or simply want to improve data governance, temporal tables can help maintain data integrity and transparency in your product inventory system.