SQL Server temporal table for historical changes only

Question

I have a table of data that changes frequently. I don’t care about changes to data that is less than 30 days old. No need to track those versions. But once the data is about 30 days old I want to track changes to it. Is this possible using a single temporal table? Or would I need to have my ‘real time’ table with everything in it and a separate ‘archive table’ that we would ETL in only the 30 day old data and turn the temporal on that on?

Answer 1

Or would I need to have my ‘real time’ table with everything in it and a separate ‘archive table’ that we would ETL in only the 30 day old data/datetime and turn the temporal on that on?

You have to use this approach since when you configure temporal tables, you cannot setup an 30 days offset on some date attribute.

Therefore, as you pointed in the initial question, the logical workaround is to create a scheduled process which MERGE changes into the archival table that has enabled TEMPORAL TABLE functionality, so it will maintain a history of changes.

Answer 2

Is this possible using a single temporal table? Or would I need to have my ‘real time’ table with everything in it and a separate ‘archive table’ that we would ETL in only the 30 day old data and turn the temporal on that on?

When a System Versioned temporal table is created it automatically creates a history table which is suffixed with '_history'. While system versioning is on there is no way to delete from the _history. The simple answer is to switch the versioning off, delete the rows from _history table, then switch system versioning back on. Like this. There are lots of articles and examples on SO and other sites.