How to prevent SQL71609 with SSDT on a new temporal table (system-versioned)

Question

I try to use SQL Server Temporal Tables within Visual Studio 2017 and SQL Server Data Tools (SSDT).

But I get immediately following error:

SQL71609: System-versioned current and history tables do not have matching schemas. Mismatched column: '[dbo].[MyTable].[ValidFrom]'

I don't see any mistake. Do I miss something?

I created a small repository on GIT HUB for reproduction

The current table is defined as:

CREATE TABLE [dbo].[MyTable]
(
    [TenantId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_MyTable_TenantId] DEFAULT 
                            CAST(SESSION_CONTEXT(N'TenantId') AS UNIQUEIDENTIFIER),
    [Rn] BIGINT IDENTITY(1,1) NOT NULL,
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [PropA] INT NOT NULL,
    [PropB] NVARCHAR(100) NOT NULL,

    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT [DF_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'),
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT [DF_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]),

    CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED ([Id]),
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MyTableHistory]))
GO

CREATE UNIQUE CLUSTERED INDEX [CIX_MyTable] ON [dbo].[MyTable]([Rn])
GO

And the history table :

CREATE TABLE [dbo].[MyTableHistory]
(
    [TenantId] UNIQUEIDENTIFIER NOT NULL,
    [Rn] BIGINT IDENTITY(1,1) NOT NULL,
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [PropA] INT NOT NULL,
    [PropB] NVARCHAR(100) NOT NULL,

    [ValidFrom] DATETIME2,
    [ValidTo] DATETIME2,
);
GO
CREATE CLUSTERED COLUMNSTORE INDEX [COLIX_MyTableHistory]
    ON [dbo].[MyTableHistory];
GO
CREATE NONCLUSTERED INDEX [IX_ImpactHistory_ValidFrom_ValidTo_Id]
    ON [dbo].[MyTableHistory] ([ValidFrom], [ValidTo], [Id]);
GO

Answer 1

Not really sure why you are getting this particular error message.
I've tested your code on db fiddle and got different errors.

BTW, please note that you don't have to write the history table yourself - if you only set it's name using the SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MyTableHistory]) and not create it, SQL Server will generate it automatically for you - as can be seen in this fiddle.

For the first attempt I've got this error:

Msg 13518 Level 16 State 1 Line 20 Setting SYSTEM_VERSIONING to ON failed because history table 'fiddle_e3d361da65804a39b041c8149132b443.dbo.MyTableHistory' has IDENTITY column specification. Consider dropping all IDENTITY column specifications and trying again.

So I've removed the identity from [Rn] column in the history table and tried again. Then I've got this error:

Msg 13530 Level 16 State 1 Line 20 Setting SYSTEM_VERSIONING to ON failed because system column 'ValidFrom' in history table 'fiddle_d6660ab11cdc448dba35790867169a14.dbo.MyTableHistory' corresponds to a period column in table 'fiddle_d6660ab11cdc448dba35790867169a14.dbo.MyTable' and cannot be nullable.

So I've changed both the ValidFrom and ValidTo columns to NOT NULL and finally got it working.

The working version is copied to here:

CREATE TABLE [dbo].[MyTableHistory]
(
    [TenantId] UNIQUEIDENTIFIER NOT NULL,
    [Rn] BIGINT NOT NULL,
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [PropA] INT NOT NULL,
    [PropB] NVARCHAR(100) NOT NULL,

    [ValidFrom] DATETIME2 NOT NULL,
    [ValidTo] DATETIME2 NOT NULL,
);

CREATE CLUSTERED COLUMNSTORE INDEX [COLIX_MyTableHistory]
    ON [dbo].[MyTableHistory];

CREATE NONCLUSTERED INDEX [IX_ImpactHistory_ValidFrom_ValidTo_Id]
    ON [dbo].[MyTableHistory] ([ValidFrom], [ValidTo], [Id]);


CREATE TABLE [dbo].[MyTable]
(
    [TenantId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_MyTable_TenantId] DEFAULT CAST(SESSION_CONTEXT(N'TenantId') AS UNIQUEIDENTIFIER),
    [Rn] BIGINT IDENTITY(1,1) NOT NULL,
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [PropA] INT NOT NULL,
    [PropB] NVARCHAR(100) NOT NULL,

    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT [DF_ValidFrom] DEFAULT CONVERT(DATETIME2, '0001-01-01'),
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT [DF_ValidTo] DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]),

    CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED ([Id]),
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MyTableHistory]))


CREATE UNIQUE CLUSTERED INDEX [CIX_MyTable] ON [dbo].[MyTable]([Rn])