I have been setting up a proof of concept system which has a SQL Server 2017 back end.
The system uses temporal tables to record asset configurations and track changes over time.
I have a Data table which is linked to the history table, let's call it dbo.MSSQL_TemporaryHistoryFor_12345678900.
So far so good. I have two issues:
Today I turned off versioning on the table so I could add a computed column. This was done and turned back on again without errors.
Now I find that I cannot query any historic data from before the change. New data is being added to the history, but there is nothing beforehand.
Looking inside SSMS, I can now see there are multiple history tables, all with the same name but with a hex suffix, e.g. dbo.MSSQL_TemporaryHistoryFor_12345678900_A0B1C2D3. They are not linked beneath the main data table. They are just floating around on their own inside the database. When I queried sys.tables, these are not shown as history tables and are not linked to the main data table.
These tables do contain the historical data which is missing.
The questions I have are therefore:
- What do these additional tables represent?
- How were they created?
- Is there any way to somehow relink these into the main history chain so I can get my historical reporting back?
It's very frustrating so any help you can provide would be gratefully received. Thanks.
Best Answer
You need to provide the name of the history table in order to maintain data continuity when turning system versioning off and on. This behavior is mentioned in the documentation for ALTER TABLE:
Here's a demo. I'll create the example table from the documentation:
This results in a history table named
MSSQL_TemporalHistoryFor_1253579504
. Now I'll disable and enable system versioning:And I'm in your exact situation:
Now I'll clean everything up:
Then create the table with a specific history table name:
Then turn system versioning off and on, but continue specifying the history table name:
Note: in your specific situation, you should be able to use this syntax to "reattach" one lost history table to your base table
No extra tables:
The takeaway
Always specify a history table name explicitly when creating temporal tables or enabling system versioning.
The MS docs now call this out specifically on the Stopping System-Versioning on a System-Versioned Temporal Table page: