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