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”.
CREATE SCHEMA App; CREATE TABLE App.Customers ( Company_ID int IDENTITY(1, 1) NOT NULL, CompanyName nvarchar(250) NOT NULL, Email varchar(250) NOT NULL, Valid_From datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL, Valid_To datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL, CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (Company_ID), PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To) ) WITH (SYSTEM_VERSIONING=ON);
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?Continue reading