Daniel Hutmacher does a little locking down:
You may have already discovered a relatively new feature in SQL Server called system-versioned temporal tables. You can have SQL Server set up a history table that keeps track of all the changes made to a table, a bit similar to what business intelligence people would call a “slowly changing dimension”.
What happens behind the scenes is that SQL Server creates a separate table that keeps track of previous versions of row changes, along with “from” and “to” timestamps. That way, you can view the contents of the table as it was at any given point in time.
But how to you version the contents of a table, while hiding things like deleted records from prying eyes?
There’s not a whole lot we can do, but Daniel shows what we are able to do.