SSDT always redeploys tables with system_versioning but doesn't update the table

287 views Asked by At

I have a couple of system_versioned tables and deploy the project via SSDT to an Azure SQL Managed Instance. For many of these system_versioned tables SSDT always recreates the publish code or shows these tables as differing in schema compare (see below image).

To me it looks like SSDT cannot update the history_retention_parameter. This has changed for some tables but not all tables for which this deployment behaviour occurs. The publish script for this table consists of the following steps:

  • dropping the system_versioning
  • print "ALTER TABLE XY"
  • do nothing
  • add system_versioning , see below excerpt:
GO
PRINT N'Dropping system-versioning from [adf].[sourceConfig]...';

GO
IF EXISTS (SELECT TOP 1 1 
           FROM   [sys].[tables]
           WHERE  [name] = N'sourceConfig'
                  AND SCHEMA_NAME(schema_id) = N'adf'
                  AND temporal_type = 2)
    BEGIN
        ALTER TABLE [adf].[sourceConfig] SET (SYSTEM_VERSIONING = OFF);
    END

GO
PRINT N'Altering [adf].[sourceConfig]...';

GO
PRINT N'Adding system-versioning to [adf].[sourceConfig]...';

GO
ALTER TABLE [adf].[sourceConfig] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[adf].[sourceConfig_HISTORY], DATA_CONSISTENCY_CHECK=ON));

GO
PRINT N'Update complete.';

Schema Compare looks as follows (it appears as the history_retention_period-parameter is not recognized): However, if I check sys.tables

schema compare for a system_versioned table

In general the tables are defined as follows:

CREATE TABLE [ecc].[A0] AS
    [someColumnes] DATATYPE NOT NULL [...],
    [odsValidFrom] DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    [odsValidTo] DATETIME2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    PRIMARY KEY CLUSTERED ([hashKey] ASC),
    PERIOD FOR SYSTEM_TIME([odsValidFrom], [odsValidTo])
    )
    WITH (
            SYSTEM_VERSIONING = ON (
                HISTORY_TABLE = [ecc].[A0_HISTORY],
                DATA_CONSISTENCY_CHECK = ON,
                HISTORY_RETENTION_PERIOD = 7 DAY
                )
            );

Visual Studio: 2019 Enterprise 16.8.1

0

There are 0 answers