Why do I have multiple (unassociated) temporal history tables?

Question

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.

Answer 1

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:

If you don't use the HISTORY_TABLE argument, the system generates a new history table matching the schema of the current table, creates a link between the two tables, and enables the system to record the history of each record in the current table in the history table.

Here's a demo. I'll create the example table from the documentation:

CREATE TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON);

This results in a history table named MSSQL_TemporalHistoryFor_1253579504. Now I'll disable and enable system versioning:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = ON);

And I'm in your exact situation:

---

Now I'll clean everything up:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.Employee;
DROP TABLE dbo.MSSQL_TemporalHistoryFor_1253579504;
DROP TABLE dbo.MSSQL_TemporalHistoryFor_1253579504_D0055BB4;

Then create the table with a specific history table name:

 CREATE TABLE dbo.Employee   
(    
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED   
  , [Name] nvarchar(100) NOT NULL  
  , [Position] varchar(100) NOT NULL   
  , [Department] varchar(100) NOT NULL  
  , [Address] nvarchar(1024) NOT NULL  
  , [AnnualSalary] decimal (10,2) NOT NULL  
  , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START  
  , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END  
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)  
 )    
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));  

Then turn system versioning off and on, but continue specifying the history table name:

ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Employee SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

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:

When turning system versioning back on, do not forget to specify the HISTORY_TABLE argument. Failing to do so will result in a new history table being created and associated with the current table. The original history table will still exist as a normal table, but won't be associated with the current table.