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.