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. :-)