Automatically expiring records in a temporal table?

Question

I have a need to store some results that are only valid for a rolling 5 day window. An application will insert or update records and the results should be viewable for the 5 day window. However, there potentially is a need to look at how the results were for prior periods. A temporal table sounded great for this as historical I can just use the SYSTEM_TIME between clause to look up historical records, and to look at the current 5 day window, I would just leave off the clause. However, there is a problem let say that some record I, was valid last week, but is no longer in the current dataset. Without me adding a step to delete old results I will still be in the resulting set as it's row end column will always have 9999-12-31, until it is updated or deleted. How can I set it to dateadd(d,5,sysutcdatetime())? Setting a default does not seem to work, and I cannot explicitly insert a value either.

Cannot insert an explicit value into a GENERATED ALWAYS column in table 'my.table'. Use INSERT with a column list to exclude the GENERATED ALWAYS column, or insert a DEFAULT into GENERATED ALWAYS column.

Note that the hint lies. The default seems to not be used.

Here is a fiddle presenting the problem.

Answer 1

This is the essence of what it means to be a system_versioning temporal table. You cannot control the row start and row end values. If you want the rows to be deleted after 5 days, you'll have to have a job to delete them.