Presenting Through Time and Space with the Baltimore SQL Server User Group

A couple weeks ago I was delighted to give my first remote user group session to the Baltimore SQL Server User Group. I want to thank the organizers for giving me the opportunity to do so, and to both the organizers and the attendees for dealing with the silliness that often comes with giving remote presentations. I think it went well. The organizers did a great job on their end and the attendees asked great questions, and gave me some excellent feedback. A good time was had all around.

Speaking of asking good questions, I wanted to follow up on a couple of specific ones that I did not have answers for during the session. Here they are:

1. On a system-versioned temporal table, does the primary key need to be clustered or can it be a non-clustered index? Are other non-clustered indexes replicated to the history table?

Indexes are handled independently for system-versioned temporal tables and their history tables. While the primary key is required for a temporal table, it can be either clustered or nonclustered. The clustered index of the history table will always be the period columns. Additional indexes can be added to or removed from the temporal table or its history table independently. Indexes created on the temporal table are not automatically created on the history table.

2. Can you join two tables using different system times.

Yes. There’s no syntax that works with the join clause itself, but you can do it via CTE, temporary tables, or subqueries like the example below.

/* Invalid syntax - the 'for system_time as of' breaks this query. */
select T.ID, 
       T.Name,  
       OldSalary = S.Salary,  
       NewSalary = T.Salary
from dbo.TemporalTest1 T 
inner join dbo.TemporalSD S  
    on T.ID = S.ID 
    for system_time as of '2018-05-20 00:00:00.0000000'

/* Subquery version that works: */
select T.ID, 
       T.Name,  
       OldSalary = S.Salary,  
       NewSalary = T.Salary
from dbo.TemporalTest1 T 
inner join ( 
    select ID, Salary  
    from dbo.TemporalSD  
    for system_time as of '2018-05-20 00:00:00.0000000' ) S  
    on T.ID = S.ID

/* CTE Version that works: */
with OldSalaryTable AS ( 
    select ID, Salary  
    from dbo.TemporalSD  
    for system_time as of '2018-05-20 00:00:00.0000000'
) 
select T.ID, 
       T.Name,  
       OldSalary = S.Salary,  
       NewSalary = T.Salary
from dbo.TemporalTest1 T 
inner join OldSalaryTable S  
    on T.ID = S.ID

The download for this presentation is available on my Speaking page. I hope you had as much fun as I did and thanks again for the opportunity. :-)

-David.

Please Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.