Avoid schema mismatch in tables with system version

Question

Looking for a workaround for:

Error: SQL71609: System-versioned current and history tables do not have matching schemes. Mismatched column: 'XXXX'. 

When trying to use SQL-2016 with system versions (Temporal) in SSDT for Visual Studio 2015.

I defined a base table:

 CREATE TABLE [dbo].[Example] ( [ExampleId] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, [ExampleColumn] VARCHAR(50) NOT NULL, [SysStartTime] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, [SysEndTime] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime) ) WITH (SYSTEM_VERSIONING=ON(HISTORY_TABLE=[history].[Example])) GO 

(Assume that the [history] schema is correctly created in SSDT). This builds great for the first time.

If I make changes later:

 CREATE TABLE [dbo].[Example] ( [ExampleId] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, [ExampleColumn] CHAR(50) NOT NULL, -- NOTE: Changed datatype [SysStartTime] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, [SysEndTime] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime) ) WITH (SYSTEM_VERSIONING=ON(HISTORY_TABLE=[history].[Example])) GO 

Then the assembly will complete with the error message above. Any change in data type, length, precision or scale will result in this error. (Including a change from VARCHAR to CHAR and VARCHAR(50) to VARCHAR(51) , changing NOT NULL to NULL does not result in an error.) Running Clean does not fix.

My current workaround is to make sure I have the latest version installed in the original control, then open SQL Server Object Explorer, expand the Projects - XXXX folder Projects - XXXX and navigate to the affected table, and then delete it. Then I need to restore the code (which removes the SSDT) ​​from the original control. This procedure is tiring, dangerous, and not what I want to do.

Has anyone found a way to fix this? This is mistake?

I am using Microsoft Visual Studio Professional 2015, version 14.0.25431.01 Update 3 with SQL Server data tools 14.0.61021.0.

Answer 1

I can reproduce this problem. We (the SQL Server development team) will work to fix this in a future version of SSDT. In the meantime, I believe that you can get around this by explicitly specifying a history table (that is, adding a history table with its desired scheme to the project), and then manually save the current and history table layout.

If you are having trouble explicitly defining the history table, try closing Visual Studio by deleting the DBMDL file in the root of the project, and then reopening the project.

Answer 2

We just survived this problem. We found a workaround by commenting out the version control elements of the table system (actually making it a regular table), building a project with a schema change that we needed (which succeeds), and then returned the version control lines of the system again (which is also successful).