How to query the first row of a system-versioned temporal table to get create timestamp and details

Question

I have a database where most of the tables have System-Versioned Temporal Tables (history tables). Often I want to query rows from the 'normal' table and get some data from the oldest record in the history table for each row in the normal table, e.g. the create timestamp and create UserId. Is there any shortcut to doing this or is something like this the right approach:

My tables are:

• dbo.Employee - primary key is EmployeeId, period cols are SysStartTime, SysEndTime
• dbo.Employee_History

Query:

select 
    -- All columns from normal table: 
    e.*   

    -- Plus columns wanted from history table: 
  , created.SysStartTime as CreatedTime
  , created.EditUserId as CreatedUserId

from dbo.Employee e 
-- Join to a derived table with the original rows for each record.
-- If a record in the normal table hasn't been changed there won't be a row in this.
left join ( 
    select * 
    from ( 
        select EmployeeId
             , SysStartTime
             , EditUserId
             , RowVersionNumber = ROW_NUMBER() over ( partition by EmployeeId order by SysStartTime asc ) 
        from Employee_History
    ) h
    -- filter to only get the oldest of each record
    where RowVersionNumber = 1
) created on u.EmployeeId = created.EmployeeId

Answer 1

I can't help you finding a better way to query the details from the first version of the record. But if it this query is going to be used a lot, maybe you should index the history table to get better performance, because by default the history table comes with a clustered index on (SysEndTime, SysStartTime), so expect a lot of information being read to get the results.

Maybe this could help you:

create index IX_EmployeeId_SysStartTime on Employee_History (
    EmployeeId ASC,
    SysStartTime ASC
)
INCLUDE (
    EditUserId
)

I can think of other ways to get the date of the record's first row on history, but just the date, not the full row. If this is enough for you, try these to see how they behave over your data:

--
select e.*, created.minSysStartTime
from Employee e
left join (
    select EmployeeId, min(SysStartTime) as minSysStartTime
    from Employee_History 
    group by EmployeeId
) created on e.EmployeeId = created.EmployeeId
--
select *
from Employee e
outer apply (
    select min(SysStartTime) as minSysStartTime
    from Employee_History c
    where e.EmployeeId = c.EmployeeId
) created

And these two queries (in my tests) only make a different execution plan when using that index I've mentioned first, otherwise they behave the same.