I recently faced an issue related to managing and maintaining my system-versioned temporal tables. Out of the blue, our dev/test database was full of weird tables. Dozens of them. With those long names, 'MSSQL_TemporalHistoryFor_' followed by several numbers and letters.
The source was obvious: they were historical tables that work together with the temporal ones to keep track of data versioning. But why so many and what should we do with them?
The Basics
Whenever we create a temporal table, a historical object will be created and linked to the main table. While the main object keeps the current version of each row, the historical object stores the old versions of each row and the timespan when it was valid.
If you do not specify the name of the historical object when you create the temporal table, the engine will create a new table with the name "MSSQL_TemporalHistoryFor_[object_id]_[suffix]". I understand it is a good practice to give a proper name to the historical object, explicitly referencing the main one and adding a suffix to tell it holds historical data. You can do that either that object already exists or not. If it doesn't, the engine will also create a new table, but this time using the name you specified in the SQL create statement.
CREATE TABLE dbo.myTemporalTab(
id bigint NOT NULL,
Description varchar(200),
initdate datetime NOT NULL,
SysStartTime datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),
CONSTRAINT PK_test PRIMARY KEY CLUSTERED (id )
)
WITH (SYSTEM_VERSIONING = ON )
-- to properly name your history table, replace the line above with this one:
-- WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.myTemporalTab_HISTORY ) )
GO
Once your temporal table is created, every time a record is updated or deleted, the old version of the record will move to the history table, including the proper timestamps to identify the period when that version was valid.
You can identify temporal tables in several ways. One of them is through SSMS, as shown below. As you see, table is tagged as system-versioned and it also references the associated historical table.
Figure 1: Checking Temporal Tables in SSMSAnother way is by running a query over SQL Server catalog objects:
--REGULAR SYSTEM-VERSIONED TABLES SELECT T.Temporal_type_desc AS Type , OBJECT_SCHEMA_NAME(T.object_id) + '.' + T.name AS TABTEMPORAL , OBJECT_SCHEMA_NAME(H.object_id) + '.' + H.name AS TABHISTORY FROM sys.tables T INNER JOIN sys.tables H ON T.history_table_id = H.object_id WHERE T.temporal_type = 2
It is important to keep in mind that the historical object will not have a clustered index if it was automatically created by the SQL Server engine. So, if you are working with a large table, it might be useful to change that. Depending on the way you use your system-version table, there is possibility the number of historical versions you collect may get much bigger than the number of active ones.
Besides doing a great job automatically auditing the data, system-versioned tables have an interesting behavior: like tables referenced in foreign key constraints, you can not drop or truncate them while system-versioning is on. This is an important consideration in this study.
Understanding What Happened
It is no news there are times we need to unit test our code and clean up some tables before doing so. If the tests involve temporal tables, it might be useful to switch system-versioning OFF, so you can easily clean up large tables. The trick here is to correctly re-enable it, as you see here:
ALTER TABLE dbo.myTemporalTab SET ( SYSTEM_VERSIONING = Off ) GO TRUNCATE TABLE dbo.myTemporalTab ALTER TABLE dbo.myTemporalTab SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.myTemporalTab_HISTORY ) ) GO
But suppose you have to repeat this process several times and you simply forget to declare the name of the historical table. The result would be the same as I found in my environment. The following image shows that result.
Figure 2: identifying possible orphaned historical tables in SSMSAs you notice, in the last test, somebody re-associated the correct historical table to the system-versioned one. But old historical tables were left behind.
If you want to track the history of those events, you can check the creation date of those tables. This is not an easy task if you don't use a naming convention for temporal and historical tables. The following script assume the existing table names would be related to the words "TEMPORAL", "HISTORICAL" or "VERSIONED".
SELECT T.create_date AS CREATION_DATE , T.temporal_type_desc AS REAL_TYPE , 'SYSTEM_VERSIONED_TEMPORAL_TABLE' AS EXPECTED_TYPE , OBJECT_SCHEMA_NAME(T.object_id) + '.' + T.name AS TAB FROM sys.objects O INNER JOIN sys.tables T ON O.object_id = T.object_id WHERE (O.name LIKE '%TEMP%' OR O.name LIKE '%HIST%' OR O.name like '%VERS%') AND T.temporal_type IN (2) UNION SELECT T.create_date AS CREATION_DATE , T.temporal_type_desc AS REAL_TYPE , 'HISTORICAL' AS EXPECTED_TYPE , OBJECT_SCHEMA_NAME(T.object_id) + '.' + T.name AS TAB FROM sys.objects O INNER JOIN sys.tables T ON O.object_id = T.object_id WHERE (O.name LIKE '%TEMP%' OR O.name LIKE '%HIST%' OR O.name like '%VERS%') AND T.temporal_type IN (0,1)
The result of this query shows the sequence of events. It clearly states that temporal and historical table were create first. Several historical objects were created after that. But, in the end, someone re-linked the two original objects, since they finish all events presenting the correct types, that is system versioned temporal table and history table, respectively.
Figure 3: querying to look for possible orphaned historical tablesA Few Words on Security
Evidently, I could avoid this issue controlling the privileges of the existing users.
Suppose you need to allow users to create tables in a given schema and execute CRUD operations on those tables. But you don't want them to turn off the system-versioning functionality. One way to do that is to give them the following privileges.
GRANT ALTER ON SCHEMA :: dbo TO User1; GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: dbo TO User1; GRANT CREATE TABLE TO User1;
Users can create/drop regular tables, execute SELECT, INSERT, UPDATE & DELETE and they can also create the temporal tables. They won't be allowed to drop the temporal tables, because they don't have the privilege to disable system-versioning. They would require the CONTROL privilege to do so.
A Different Scenario
In my case study, the original goal was to truncate data from the main table. So I assume the versioning data could be discarded, too. In the end, I preferred to drop those orphaned historical objects , since they would not be used anymore. End of the story.
But let's suppose we have orphaned historical objects, but no data can be lost. What should we do if we could not purge that history?
First of all, we must identify the list of historical objects related to a given temporal table, as we did using the previous SQL statement. After that, we must understand the chronological order of row versions. This means sorting data by the time that version was deprecated. You can find this information in column "SysEndTime".
So, we have to put together the entire history of data versions and insert them in the table that is going to be the official historical object.
First, I execute the previous SELECT statement to identify the objects involved in this scenario. This is the result I am looking for:
Figure 4: querying for historical tables related to object "69010499"In this scenario, table "myTemporalTab" is correctly associated to "myTemporalTab_HISTORY". But there are orphaned historical tables, meaning something went wrong for sometime.
The next step is to get ready to collect all system-version data. I used a temporary table to collect the data.
CREATE TABLE #temp ( sourceTabvarchar(100)NOT NULL, id bigint NOT NULL, Description varchar(200)NULL, initdate datetime NOT NULL, SysStartTimedatetime2(7)NOT NULL, SysEndTime datetime2(7)NOT NULL ) GO INSERT INTO #TEMP SELECT 'myTemporalTab_HISTORY' sourceTab, * FROM dbo.myTemporalTab_HISTORY UNION SELECT 'MSSQL_TemporalHistoryFor_690101499' sourceTab, * FROM dbo.MSSQL_TemporalHistoryFor_690101499 UNION SELECT 'MSSQL_TemporalHistoryFor_690101499_D48FA041' sourceTab, * FROM dbo.MSSQL_TemporalHistoryFor_690101499_D48FA041 UNION SELECT 'MSSQL_TemporalHistoryFor_690101499_E4765B21' sourceTab, * FROM dbo.MSSQL_TemporalHistoryFor_690101499_E4765B21 GO
If system-versioning is on, remember to turn it off before moving on.
In fact, I am going to reuse table "dbo.myTemporalTab_HISTORY" and therefore I have to truncate that table before inserting the proper data.
To end the whole operation, set system-versioning is on again.
The script below does this entire operation.
ALTER TABLE dbo.myTemporalTab SET ( SYSTEM_VERSIONING = OFF ) GO TRUNCATE TABLE dbo.myTemporalTab_HISTORY GO INSERT INTO dbo.myTemporalTab_HISTORY SELECT id, Description, initdate , SysStartTime, SysEndTime FROM #TEMP ORDER BY SysEndTime GO ALTER TABLE dbo.myTemporalTab SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.myTemporalTab_HISTORY ) ) GO --Don't forget to drop old objects
Finally, we can check if operation is working fine by querying your temporal data.
Maybe we are dealing with production and can not make any change to data. But you can query data using features related to temporal tables. For instance, you can see the entire history of a given record, as presented here:
--show all history for ID 4 SELECT * FROM dbo.myTemporalTab FOR SYSTEM_TIME BETWEEN '2021-01-01' AND '2021-03-31' WHERE Id = 4 ORDER BY SysStartTime DESC;
Figure 5: checking the history of ID = 4Final Comments
System-version temporal tables are pretty easy to create, maintain and query. For me, this is the real beauty of it.
One might complain the collected data is not enough. But you can always improve that using the proper table design and letting the application provide the extra data you want to collect.
