1

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.

2
  • Couldn't you filter based on when the row was inserted and ignore anything over 5 days?
    – bbaird
    Jul 9 at 18:22
  • While sure, I as the guy working on the database know this fact. An end-user may not. I can write a view and have that permissioned for the end-users and then use instead-of triggers to add the hidden expiry column, but then it's the samething as a temporal table except I have to do all the work.
    – Michael B
    Jul 9 at 18:57
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.