Why do I have multiple (unassociated) temporal history tables?Query strategies using SQL Server 2016...

How can I install sudo without using su?

Caruana vs Carlsen game 10 (WCC) why not 18...Nxb6?

How should I handle players who ignore the session zero agreement?

Error in a formula field

My cat mixes up the floors in my building. How can I help him?

Difference between `vector<int> v;` and `vector<int> v = vector<int>();`

Broken patches on a road

Do authors have to be politically correct in article-writing?

Why would space fleets be aligned?

How to avoid Replace substituting subscripts?

Can an insurance company drop you after receiving a bill and refusing to pay?

Why did the villain in the first Men in Black movie care about Earth's Cockroaches?

What are jets (units)?

Explain the objections to these measures against human trafficking

Blindfold battle as a gladiatorial spectacle - what are the tactics and communication methods?

Does static makes difference for const local variable?

How do I say "Brexit" in Latin?

How to prevent cleaner from hanging my lock screen in Ubuntu 16.04

Can I write a book of my D&D game?

It took me a lot of time to make this, pls like. (YouTube Comments #1)

A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?

Why is working on the same position for more than 15 years not a red flag?

What is the wife of a henpecked husband called?

Can we use the stored gravitational potential energy of a building to produce power?



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


Query strategies using SQL Server 2016 system-versioned temporal tables for Slowly-Changing DimensionsWill Temporal Tables be included in the Standard Edition of SQL Server 2016?Why do temporal tables log the begin time of the transaction?Deadlocks on primary key row locks race condition with temporal tablesChange the behavior of temporal tables to log actual value changes rather than dummy updatesSQL Server 2016, temporal tables and compressed indexesWhy we need to optimize CONTAINED IN clause of FOR_SYSTEM TIME using check constraint?SQL Temporal Tables Include Current StateError on rename for a column on a temporal tablePoor temporal table performance on older values













4















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.










share|improve this question









New contributor




MrB is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 1





    It might be helpful if you provide the commands you ran before you got into this state.

    – LowlyDBA
    1 hour ago











  • If you don't explicitly name the history table, SQL Server will create a new one. Always. And it won't conflict names with existing tables for the same reason it appends temp tables with a long _____hex suffix. It has no memory that temporal was previously enabled for the table, so it doesn't automatically go back and stitch those tables together for you. You can do that but it will probably be a very tedious affair, and you'd need to lock the base table with serializable to prevent any changes that would just put you in an infinite loop.

    – Aaron Bertrand
    1 hour ago













  • But according to docs.microsoft.com/en-us/sql/relational-databases/tables/…, using the command ALTER TABLE dbo.Department SET (SYSTEM_VERSIONING = OFF) should not lose any data. In this case, the data is virtually lost since it is no longer queryable. This was the command I ran.

    – MrB
    1 hour ago











  • It's not lost as in the history table doesn't get dropped. You can still get the historical data, just not with the built-in syntactic sugar. When you re-enable system_versioning you should be able to point it at (one) old table?

    – Aaron Bertrand
    1 hour ago













  • That's an interesting idea. What syntax would one use to point it at the correct history table?

    – MrB
    1 hour ago
















4















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.










share|improve this question









New contributor




MrB is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
















  • 1





    It might be helpful if you provide the commands you ran before you got into this state.

    – LowlyDBA
    1 hour ago











  • If you don't explicitly name the history table, SQL Server will create a new one. Always. And it won't conflict names with existing tables for the same reason it appends temp tables with a long _____hex suffix. It has no memory that temporal was previously enabled for the table, so it doesn't automatically go back and stitch those tables together for you. You can do that but it will probably be a very tedious affair, and you'd need to lock the base table with serializable to prevent any changes that would just put you in an infinite loop.

    – Aaron Bertrand
    1 hour ago













  • But according to docs.microsoft.com/en-us/sql/relational-databases/tables/…, using the command ALTER TABLE dbo.Department SET (SYSTEM_VERSIONING = OFF) should not lose any data. In this case, the data is virtually lost since it is no longer queryable. This was the command I ran.

    – MrB
    1 hour ago











  • It's not lost as in the history table doesn't get dropped. You can still get the historical data, just not with the built-in syntactic sugar. When you re-enable system_versioning you should be able to point it at (one) old table?

    – Aaron Bertrand
    1 hour ago













  • That's an interesting idea. What syntax would one use to point it at the correct history table?

    – MrB
    1 hour ago














4












4








4








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.










share|improve this question









New contributor




MrB is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.












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.







sql-server sql-server-2017 temporal-tables






share|improve this question









New contributor




MrB is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.











share|improve this question









New contributor




MrB is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









share|improve this question




share|improve this question








edited 1 min ago









jadarnel27

6,01811938




6,01811938






New contributor




MrB is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.









asked 1 hour ago









MrBMrB

232




232




New contributor




MrB is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.





New contributor





MrB is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.






MrB is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.








  • 1





    It might be helpful if you provide the commands you ran before you got into this state.

    – LowlyDBA
    1 hour ago











  • If you don't explicitly name the history table, SQL Server will create a new one. Always. And it won't conflict names with existing tables for the same reason it appends temp tables with a long _____hex suffix. It has no memory that temporal was previously enabled for the table, so it doesn't automatically go back and stitch those tables together for you. You can do that but it will probably be a very tedious affair, and you'd need to lock the base table with serializable to prevent any changes that would just put you in an infinite loop.

    – Aaron Bertrand
    1 hour ago













  • But according to docs.microsoft.com/en-us/sql/relational-databases/tables/…, using the command ALTER TABLE dbo.Department SET (SYSTEM_VERSIONING = OFF) should not lose any data. In this case, the data is virtually lost since it is no longer queryable. This was the command I ran.

    – MrB
    1 hour ago











  • It's not lost as in the history table doesn't get dropped. You can still get the historical data, just not with the built-in syntactic sugar. When you re-enable system_versioning you should be able to point it at (one) old table?

    – Aaron Bertrand
    1 hour ago













  • That's an interesting idea. What syntax would one use to point it at the correct history table?

    – MrB
    1 hour ago














  • 1





    It might be helpful if you provide the commands you ran before you got into this state.

    – LowlyDBA
    1 hour ago











  • If you don't explicitly name the history table, SQL Server will create a new one. Always. And it won't conflict names with existing tables for the same reason it appends temp tables with a long _____hex suffix. It has no memory that temporal was previously enabled for the table, so it doesn't automatically go back and stitch those tables together for you. You can do that but it will probably be a very tedious affair, and you'd need to lock the base table with serializable to prevent any changes that would just put you in an infinite loop.

    – Aaron Bertrand
    1 hour ago













  • But according to docs.microsoft.com/en-us/sql/relational-databases/tables/…, using the command ALTER TABLE dbo.Department SET (SYSTEM_VERSIONING = OFF) should not lose any data. In this case, the data is virtually lost since it is no longer queryable. This was the command I ran.

    – MrB
    1 hour ago











  • It's not lost as in the history table doesn't get dropped. You can still get the historical data, just not with the built-in syntactic sugar. When you re-enable system_versioning you should be able to point it at (one) old table?

    – Aaron Bertrand
    1 hour ago













  • That's an interesting idea. What syntax would one use to point it at the correct history table?

    – MrB
    1 hour ago








1




1





It might be helpful if you provide the commands you ran before you got into this state.

– LowlyDBA
1 hour ago





It might be helpful if you provide the commands you ran before you got into this state.

– LowlyDBA
1 hour ago













If you don't explicitly name the history table, SQL Server will create a new one. Always. And it won't conflict names with existing tables for the same reason it appends temp tables with a long _____hex suffix. It has no memory that temporal was previously enabled for the table, so it doesn't automatically go back and stitch those tables together for you. You can do that but it will probably be a very tedious affair, and you'd need to lock the base table with serializable to prevent any changes that would just put you in an infinite loop.

– Aaron Bertrand
1 hour ago







If you don't explicitly name the history table, SQL Server will create a new one. Always. And it won't conflict names with existing tables for the same reason it appends temp tables with a long _____hex suffix. It has no memory that temporal was previously enabled for the table, so it doesn't automatically go back and stitch those tables together for you. You can do that but it will probably be a very tedious affair, and you'd need to lock the base table with serializable to prevent any changes that would just put you in an infinite loop.

– Aaron Bertrand
1 hour ago















But according to docs.microsoft.com/en-us/sql/relational-databases/tables/…, using the command ALTER TABLE dbo.Department SET (SYSTEM_VERSIONING = OFF) should not lose any data. In this case, the data is virtually lost since it is no longer queryable. This was the command I ran.

– MrB
1 hour ago





But according to docs.microsoft.com/en-us/sql/relational-databases/tables/…, using the command ALTER TABLE dbo.Department SET (SYSTEM_VERSIONING = OFF) should not lose any data. In this case, the data is virtually lost since it is no longer queryable. This was the command I ran.

– MrB
1 hour ago













It's not lost as in the history table doesn't get dropped. You can still get the historical data, just not with the built-in syntactic sugar. When you re-enable system_versioning you should be able to point it at (one) old table?

– Aaron Bertrand
1 hour ago







It's not lost as in the history table doesn't get dropped. You can still get the historical data, just not with the built-in syntactic sugar. When you re-enable system_versioning you should be able to point it at (one) old table?

– Aaron Bertrand
1 hour ago















That's an interesting idea. What syntax would one use to point it at the correct history table?

– MrB
1 hour ago





That's an interesting idea. What syntax would one use to point it at the correct history table?

– MrB
1 hour ago










1 Answer
1






active

oldest

votes


















3














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:



enter image description here





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:



enter image description here



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.






share|improve this answer


























  • Thanks. This is exactly the issue. I thought SQL Server would be a bit cuter with regards to turning versioning off and on again, especially as the docs do not call out the need to do anything explicitly to keep historical continuity.

    – MrB
    54 mins ago











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});






MrB is a new contributor. Be nice, and check out our Code of Conduct.










draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231047%2fwhy-do-i-have-multiple-unassociated-temporal-history-tables%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









3














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:



enter image description here





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:



enter image description here



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.






share|improve this answer


























  • Thanks. This is exactly the issue. I thought SQL Server would be a bit cuter with regards to turning versioning off and on again, especially as the docs do not call out the need to do anything explicitly to keep historical continuity.

    – MrB
    54 mins ago
















3














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:



enter image description here





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:



enter image description here



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.






share|improve this answer


























  • Thanks. This is exactly the issue. I thought SQL Server would be a bit cuter with regards to turning versioning off and on again, especially as the docs do not call out the need to do anything explicitly to keep historical continuity.

    – MrB
    54 mins ago














3












3








3







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:



enter image description here





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:



enter image description here



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.






share|improve this answer















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:



enter image description here





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:



enter image description here



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.







share|improve this answer














share|improve this answer



share|improve this answer








edited 3 mins ago

























answered 1 hour ago









jadarnel27jadarnel27

6,01811938




6,01811938













  • Thanks. This is exactly the issue. I thought SQL Server would be a bit cuter with regards to turning versioning off and on again, especially as the docs do not call out the need to do anything explicitly to keep historical continuity.

    – MrB
    54 mins ago



















  • Thanks. This is exactly the issue. I thought SQL Server would be a bit cuter with regards to turning versioning off and on again, especially as the docs do not call out the need to do anything explicitly to keep historical continuity.

    – MrB
    54 mins ago

















Thanks. This is exactly the issue. I thought SQL Server would be a bit cuter with regards to turning versioning off and on again, especially as the docs do not call out the need to do anything explicitly to keep historical continuity.

– MrB
54 mins ago





Thanks. This is exactly the issue. I thought SQL Server would be a bit cuter with regards to turning versioning off and on again, especially as the docs do not call out the need to do anything explicitly to keep historical continuity.

– MrB
54 mins ago










MrB is a new contributor. Be nice, and check out our Code of Conduct.










draft saved

draft discarded


















MrB is a new contributor. Be nice, and check out our Code of Conduct.













MrB is a new contributor. Be nice, and check out our Code of Conduct.












MrB is a new contributor. Be nice, and check out our Code of Conduct.
















Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231047%2fwhy-do-i-have-multiple-unassociated-temporal-history-tables%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

“%fieldName is a required field.”, in Magento2 REST API Call for GET Method Type The Next...

How to change City field to a dropdown in Checkout step Magento 2Magento 2 : How to change UI field(s)...

變成蝙蝠會怎樣? 參考資料 外部連結 导航菜单Thomas Nagel, "What is it like to be a...