如何在新的时间表上使用SSDT预防SQL71609(系统反复)

Question

我尝试在Visual Studio 2017和SQL Server Data Tools（SSDT）中使用SQL Server临时表。

但是我立即遵循错误：

sql71609：系统反复和历史表没有 匹配的模式。不匹配的列：'[dbo]。[mytable]。[有效From]'

我看不到任何错误。我想念什么吗？

我在Git Hub上创建了一个小型存储库

当前表被定义为：

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

和历史表：

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

不确定为什么您会收到此特定的错误消息。
我已经在DB小提琴上测试了您的代码，并有不同的错误。

顺便说一句，请注意，您不必自己编写历史表 - 如果仅使用SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MyTableHistory])设置其名称而不是创建它，那么SQL Server将为您自动生成它 - 如此Fiddle所示。

对于第一次尝试，我有一个错误：

msg 13518级别16状态1线20 设置system_versing to on失败，因为历史表'fiddle_e3d361da65804a39b041c8149132b443.dbo.mytablehistory'具有身份列规范。考虑删除所有身份列规格并重试。

因此，我从历史表中的[Rn]列中删除了identity，然后再次尝试。然后我有一个错误：

msg 13530级别16状态1线20 设置System_vertion to On失败，因为历史表中的系统列'有效from'fiddle_d66660ab11cdc448dba35790867169a14.dbo.mytablehistory'对应于表'table'fiddle_d6666666660ab11cdcdc448dba oble and conse

所以我将ValidFrom和ValidTo列同时更改为NOT NULL，并最终使它起作用。

工作版本已复制到这里：

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])