Fhyujk

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.

The docs should really be more clear on this, but 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 disabled 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));  

The 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.

I've submitted a PR to MS Docs to add a reminder about this behavior on the Stopping System-Versioning on a System-Versioned Temporal Table page.