SQL Server Temporal Tables Advanced Query Strategies

In parts one and two of this series we learned how to create or convert a temporal table and basics on how to query history. Once you start using temporal tables it doesn’t take long before you are pushing the basics, this is some of the tricks that I’ve used to get the most out of temporal tables.

Part 1 – Introduction to SQL Server Temporal Tables

Part 2 – Converting an Existing SQL Server Temporal Table

Part 3 – SQL Server Temporal Tables Advanced Query Strategies

Saving Data

Saving data in temporal tables is the same as normal tables but it is useful to control when a history record is written. Let’s say your application is using a standard UPDATE SQL statement and a user double clicks the save button by accident or some other scenario where the update is executed twice. What does the history table look like then? The answer is you will get two history records for one change.

How can we prevent what is essentially a duplicate record? There are two basic strategies, one is to update your application to check if anything has changed before calling the database. The other is to update your SQL statement to only update when something has changed, I like to do this with a merge statement. It’s important to note a basic merge statement on its own, but you can construct it in a way to prevent it.

MERGE [ApplicationUser] AS target
USING (SELECT @userId, @firstName, @lastName, @jobTitle, @lastUpdatedBy)
AS source (UserId, FirstName, LastName, JobTitle, LastUpdatedBy)
ON target.UserId = source.UserId
WHEN MATCHED AND target.FirstName <> source.FirstName OR 
target.LastName <> source.LastName OR target.JobTitle <> source.JobTitle
THEN UPDATE SET FirstName = source.FirstName, LastName = source.LastName, JobTitle = source.JobTitle, LastUpdatedBy = source.LastUpdatedBy;

If you run this script multiple times in a row with the same values, you will not get additional history records because we added additional requirements by doing a WHEN MATCHED AND instead of WHEN MATCHED THEN.

One last note on saving data is that the ValidFrom is timestamp is tied to the transaction scope so if you are updating multiple rows or multiple tables in one transaction, they will have the same ValidFrom which can be very useful.

Querying Across Joined Tables

Let’s add another table to our example to demonstrate some more advanced querying and add a table for user addresses.

CREATE TABLE [dbo].[ApplicationUserAddress] (
    [UserId]        NVARCHAR (20) NOT NULL,
    [StreetAddress] NVARCHAR (50) NULL,
    [City]          NVARCHAR (50) NULL,
    [State]         NVARCHAR (50) NULL,
    [PostalCode]    NVARCHAR (10) NULL,
    [Country]       NVARCHAR (50) NULL,
    [ValidFrom]     datetime2(7) GENERATED ALWAYS AS ROW START  NOT NULL,
    [ValidTo]       datetime2(7) GENERATED ALWAYS AS ROW END    NOT NULL, 
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]),
    CONSTRAINT [PK_ApplicationUserAddress] PRIMARY KEY CLUSTERED ([UserId] ASC),
    CONSTRAINT [FK_ApplicationUserAddress_ApplicationUser] FOREIGN KEY ([UserId]) REFERENCES [dbo].[ApplicationUser] ([UserId])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[ApplicationUserAddressHistory], DATA_CONSISTENCY_CHECK=ON));

For easy in demonstrating what’s happening I’m also going to save both tables in one transaction, updated a user and address 3 times including the save number in both the first name and street address for clarity when we are querying history data. First, we’ll start with a simple query for most recent records.

SELECT      au.FirstName,
            aua.StreetAddress,
            au.ValidFrom as UserValidFrom,
            aua.ValidFrom as AddressValidFrom
FROM        [dbo].[ApplicationUser] au
LEFT JOIN   [dbo].[ApplicationUserAddress] aua on aua.UserId = au.UserId
WHERE       au.UserId = '1234'

Next let’s query the history for this user, a note if you use aliasing, you need the FOR SYSTEM_TIME before the alias between the table name and the alias.

SELECT      au.FirstName,
            aua.StreetAddress,
            au.ValidFrom as UserValidFrom,
            aua.ValidFrom as AddressValidFrom
FROM        [dbo].[ApplicationUser] FOR SYSTEM_TIME ALL au
LEFT JOIN   [dbo].[ApplicationUserAddress] aua on aua.UserId = au.UserId
WHERE       au.UserId = '1234'
ORDER BY    au.ValidFrom DESC

As you can see this isn’t exactly the result we are looking for. We are getting history for the user table but not for the address table. For the next query I’m going to fix that join but I’m going to limit to “Save 2” history records.

DECLARE @asOf DATETIME2 = '2022-11-21 23:41:26.5846378';
SELECT      au.FirstName,
            aua.StreetAddress,
            au.ValidFrom as UserValidFrom,
            aua.ValidFrom as AddressValidFrom
FROM        [dbo].[ApplicationUser] FOR SYSTEM_TIME AS OF @asOf au
LEFT JOIN   [dbo].[ApplicationUserAddress] FOR SYSTEM_TIME AS OF @asOf aua on aua.UserId = au.UserId
WHERE       au.UserId = '1234'
ORDER BY    au.ValidFrom DESC

The reason we were not getting history records is that the for system time doesn’t apply to the whole query, just the part of the of the query that the FOR SYSTEM_TIME was associated with. So if we go back to our original goal of querying the whole history you might be tempted to use ua.ValidFrom in your join like this:

SELECT      au.FirstName,
            aua.StreetAddress,
            au.ValidFrom as UserValidFrom,
            aua.ValidFrom as AddressValidFrom
FROM        [dbo].[ApplicationUser] FOR SYSTEM_TIME ALL au
LEFT JOIN   [dbo].[ApplicationUserAddress] FOR SYSTEM_TIME AS OF vs.ValidFrom aua on aua.UserId = au.UserId
WHERE       au.UserId = '1234'
ORDER BY    au.ValidFrom DESC

Unfortunately, this will result in a syntax error. You can only pass a variable or a constant to FOR SYSTEM_TIME. As a work around you can use a table valued function to get the same result, so the function would look like this:

CREATE OR ALTER FUNCTION [dbo].[AddressAsOfDate] (@userId nvarchar(20), @asOfDate datetime2)
RETURNS TABLE
AS RETURN
SELECT      *
FROM        [dbo].[ApplicationUserAddress] FOR SYSTEM_TIME AS OF @asOfDate
WHERE       UserId = @userId

And now we can query out the user history as a list with the addresses at the time of each save.

SELECT      au.FirstName,
            aua.StreetAddress,
            au.ValidFrom as UserValidFrom,
            aua.ValidFrom as AddressValidFrom
FROM        [dbo].[ApplicationUser] FOR SYSTEM_TIME ALL au
CROSS APPLY [dbo].[AddressAsOfDate] (au.UserId, au.ValidFrom) aua
WHERE       au.UserId = '1234'
ORDER BY    au.ValidFrom DESC

You might notice though that earlier there were more user saves, those save history records are from before I added an address. A way you can make this behave like a left join is to put a left join in the function.

CREATE OR ALTER FUNCTION [dbo].[AddressAsOfDate] (@userId nvarchar(20), @asOfDate datetime2)
RETURNS TABLE
AS RETURN
SELECT      aua.*
FROM        [dbo].[ApplicationUser] au
LEFT JOIN   [dbo].[ApplicationUserAddress] FOR SYSTEM_TIME AS OF @asOfDate aua ON aua.UserId = au.UserId
WHERE       au.UserId = @userId

With the function changed like this our results are now this:

Conclusion

With these saving tricks you should be able to minimize unnecessary records in the history table and should be able to join temporal tables to maximum effect.

Part 1 – Introduction to SQL Server Temporal Tables

Part 2 – Converting an Existing SQL Server Temporal Table

Part 3 – SQL Server Temporal Tables Advanced Query Strategies

About Intertech

Intertech is a Software Development Consulting Firm that provides single and multiple turnkey software development teams, available on your schedule and configured to achieve success as defined by your requirements independently or in co-development with your team. Intertech teams combine proven full-stack, DevOps, Agile-experienced lead consultants with Delivery Management, User Experience, Software Development, and QA experts in Business Process Automation (BPA), Microservices, Client- and Server-Side Web Frameworks of multiple technologies, Custom Portal and Dashboard development, Cloud Integration and Migration (Azure and AWS), and so much more. Each Intertech employee leads with the soft skills necessary to explain complex concepts to stakeholders and team members alike and makes your business more efficient, your data more valuable, and your team better. In addition, Intertech is a trusted partner of more than 4000 satisfied customers and has a 99.70% “would recommend” rating.

Estimating

+651.288.7000

info@intertech.com

1575 Thomas Center Drive, Eagan, MN 55122

Request A Quote

SQL Server Temporal Tables Advanced Query Strategies