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



  • I have a database where most of the tables have https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15 (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



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2