In SQL Server, if you have a temporal table and a history table, how do you find the original values for a record if you do not know the date/time that record was first created? If the record was never edited, then it won't have a value in the history table and the original record is in the temporal table. If it was edited, the original record is in the history table but with valid_from and valid_to dates that you don't know. Is there a parameter in the FOR SYSTEM_TIME clause that returns the record as it appeared when it was first added to the table, regardless of whether it has changed since?
Just one note on the answer. You mention this :
If the record was never edited, then it won't have a value in the history table and the original record is in the temporal table. If it was edited, the original record is in the history table
A huge benefit of using Temporal Tables is that it acts as a single table. It is not two tables where you have to do unions to work out which table you should be looking at. If you were creating a history table manually (e.g. A roll your own audit solution), then you would have to query across two different tables.
Now onto your solution. The easiest way would be to use the SYSTEM_TIME ALL flag. It works like so :
SELECT TOP 1 *
FROM Person
FOR SYSTEM_TIME ALL
WHERE Id = 1
ORDER BY ValidFrom ASC
This returns ALL historic records for a particular ID. But because we order by ValidFrom and then select the TOP 1, we actually return the very first entry.
And again, this works regardless of whether that first record is in the "current" table or the history table.
More info here : https://dotnetcoretutorials.com/2021/12/11/temporal-tables-in-sql-server/