Create an Audit for Data Using a Temporal Table

Recently someone has been changing data in a table on a legacy application written more than a decade ago. While the DEV team works on a solution to log user access for this, more importantly I need to easily get the original data back into the table, quickly. I can accomplish this by converting the table to a SYSTEM-VERSIONED TEMPORAL TABLE. A TEMPORAL TABLE attaches a history table to the original one, offering just the solution I quickly need.

In this post I will share my code to:

• Create a NEW temporal table.
• CONVERT an EXISTING table to temporal.

I already know there are no triggers on my table and it does not participate in a cascade delete, so I’m expecting NOT to run into any performance or data rollback issues.

Once I have completed my task, I will have my original table of data rows aligned in columns with its keys, constraints, indexes, and statistics. AND– a new history table with copies of data (upon change) and its own copy of constraints, indexes and statistics.

CREATE A NEW TEMPORAL TABLE

If this table did not already exist, I could create it newly, using this code:

CREATE TABLE [dbo].[MyTable](
[ID] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

--   ALL OTHER COLUMNS GO HERE
[Column1] [VARCHAR](50) NULL,
[Column2] [VARCHAR](50) NULL,

-- REQUIRED CODE
[SysTimeStart] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
[SysTimeEnd] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (SysTimeStart,SysTimeEnd))  ON [PRIMARY]
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History)) ; --<<< CHANGE TABLENAME

Optionally, I may want to find out the Primary Key (PK) name that was auto-generated and change that.

-- EXAMPLE to change PK name
EXEC sp_rename 'MyTable.PK__MyTable__9999999999999', 'PK_MyTable_ID'; 

CONVERT AN EXISTING TABLE TO TEMPORAL

Since my table already exists, I just need to add the history table to it and wire it up.

-- NOTE I RUN EACH LINE COMMAND INDIVIDUALLY.
ALTER TABLE dbo.MyTable ADD [SysTimeStart] datetime2;
ALTER TABLE dbo.MyTable ADD [SysTimeEnd] datetime2;

UPDATE dbo.MyTable SET [SysTimeStart] = '19000101 00:00:00.0000000', [SysTimeEnd] = '99991231 23:59:59.9999999';

ALTER TABLE dbo.MyTable ALTER COLUMN [SysTimeStart] datetime2 NOT NULL;
ALTER TABLE dbo.MyTable ALTER COLUMN [SysTimeEnd] datetime2 NOT NULL;

ALTER TABLE dbo.MyTable ADD PERIOD FOR SYSTEM_TIME (SysTimeStart, SysTimeEnd);
ALTER TABLE dbo.MyTable SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History, DATA_CONSISTENCY_CHECK = ON));

I want to verify that my table is working so I’m going to insert a couple rows and then change one.

INSERT INTO dbo.MyTable (Column1, Column2)
VALUES ('AAA111','AAA222'),('BBB111','BBB222');

SELECT * FROM dbo.MyTable 
SELECT * FROM dbo.MyTable_History ORDER BY SysTimeEnd DESC;

Now to make an update to change something.

UPDATE dbo.MyTable SET Column1 = 'Testing' WHERE ID = 2 

SELECT * FROM dbo.MyTable 
SELECT * FROM dbo.MyTable_History ORDER BY SysTimeEnd DESC;

DROP TEMPORAL TABLE ERROR

Oooops! The regular DROP TABLE command does not work with temporal tables. What do I do now?

DROP TABLE dbo.MyTable;

I must separate my history table before I can drop the table. I like this option, because if for some reason I want to rebuild my main table and not lose any of the historical data, I can easily do that.

-- This separates the tables so they can be dropped or modified.
ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = OFF);

And I can reconnect the HISTORY table later, using this command:

ALTER TABLE dbo.MyTable SET (SYSTEM_VERSIONING = ON
 (HISTORY_TABLE=dbo.MyTable_History,DATA_CONSISTENCY_CHECK=[ON/OFF])
);

When I want to know more about temporal tables, I refer to this white paper at Microsoft:

Temporal Tables – SQL Server | Microsoft Learn