Deployment fails when system versioning table needs update and there's a new schema #309
Description
- SqlPackage or DacFx Version:
- .NET Framework (Windows-only) or .NET Core:
- Environment (local platform and source/target platforms):
Steps to Reproduce:
- Use the following scripts in SQL Project and deploy
CREATE SCHEMA [sc1]
GO
CREATE TABLE [sc1].[Table1] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[CreatedDate] DATETIME CONSTRAINT [DF_Table1_CreatedDate] DEFAULT GETUTCDATE() NOT NULL,
[LastUpdatedDate] DATETIME CONSTRAINT [DF_Table1_LastUpdatedDate] DEFAULT GETUTCDATE() NOT NULL,
[SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
[SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
[EffectiveDate] DATE NOT NULL,
[SubFundCode] NVARCHAR (10) NULL,
CONSTRAINT [PK_Table1_EffectiveDate_Id] PRIMARY KEY NONCLUSTERED ([EffectiveDate] ASC, [Id] ASC),
INDEX [CCSIX_Table1] CLUSTERED COLUMNSTORE,
PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[sc1].[Table1History], DATA_CONSISTENCY_CHECK=ON));
GO
- Then add a new table and schema, make a change in the existing system versioning table and deploy
REATE SCHEMA [sc2]
GO
CREATE SCHEMA [sc1]
GO
CREATE TABLE [sc2].[Table2] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[LastUpdatedDate] DATETIME CONSTRAINT [DF_Table2_LastUpdatedDate] DEFAULT GETUTCDATE() NOT NULL,
[SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
[SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
[DataSetId] UNIQUEIDENTIFIER NOT NULL,
[ProcessId] INT NULL,
[EffectiveDate] DATE NOT NULL,
CONSTRAINT [PK_Table2_Id] PRIMARY KEY CLUSTERED ([Id] ASC),
PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[sc2].[Table2History], DATA_CONSISTENCY_CHECK=ON, HISTORY_RETENTION_PERIOD=12 MONTH));
GO
CREATE TABLE [sc1].[Table1] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[CreatedDate] DATETIME CONSTRAINT [DF_Table1_CreatedDate] DEFAULT GETUTCDATE() NOT NULL,
[LastUpdatedDate] DATETIME CONSTRAINT [DF_Table1_LastUpdatedDate] DEFAULT GETUTCDATE() NOT NULL,
[SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START NOT NULL,
[SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END NOT NULL,
[EffectiveDate] DATE NOT NULL,
[SubFundCode] NVARCHAR (10) NULL,
CONSTRAINT [PK_Table1_EffectiveDate_Id] PRIMARY KEY NONCLUSTERED ([EffectiveDate] ASC, [Id] ASC),
INDEX [CCSIX_Table1] CLUSTERED COLUMNSTORE,
PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[sc1].[Table1History], DATA_CONSISTENCY_CHECK=ON, HISTORY_RETENTION_PERIOD=12 MONTH));
GO
Expected: deploys successfully
Actual: deploy fails because schema sc2 is created after the new table
Did this occur in prior versions? If not - which version(s) did it work in?
(DacFx/SqlPackage/SSMS/Azure Data Studio)