Sqlpackage.exe is dropping default constraints and computed columns

Question

I'm deploying database schema-only (no data) changes to our Azure Sql Managed Instance via Azure DevOps task SqlAzureDacpacDeployment@1, and for some tables it is always dropping and re-creating computed columns and/or default constraints. Some of these tables are very large and to drop/re-add a computed column or default constraint is very time consuming.

Anyone know why this is happening, and is there a way to prevent it? As mentioned, this is a schema-only deployment, there is no data being inserted.

This is a snippet of what is being executed by the DevOps task:

"C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /Action:Publish 
    /SourceFile:"D:\a\1\drop\CCDatabase\bin\Release\CCDatabase.dacpac" /TargetServerName:"***" 
    /TargetDatabaseName:"***" /AccessToken:"********" /p:TreatVerificationErrorsAsWarnings=True 
    /p:DropObjectsNotInSource=True /p:DoNotDropObjectTypes=Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials;LinkedServerLogins;LinkedServers;
    /p:BlockOnPossibleDataLoss=False

This is what I see in the log, and I've verified the columns/constraints are being dropped via Sql Profiler.

Dropping Default Constraint [VZ].[DF_Location_CreatedDateUtc]...
Dropping Default Constraint [VZ].[DF_Location_Hold_CreatedDateUtc]...
Dropping Default Constraint [VZ].[DF_Location_Stage_CreatedDateUtc]...
Altering Table [CRM].[QuarterlyGoal]...
Altering Table [D365].[CUSTCUSTOMERV3STAGING]...
Starting rebuilding table [SM].[TechnicianAvailability]...
Starting rebuilding table [VZ].[Location]...
Starting rebuilding table [VZ].[Location_Hold]...
Starting rebuilding table [VZ].[Location_Stage]...

Here is an example of a table that is being rebuilt:

This one had the default constraint [DF_Location_Hold_CreatedDateUtc] dropped and the table data copy to a temp table, and then renamed back to original table name:

Table definition:

CREATE TABLE [VZ].[Location_Hold]
(
    [VehicleNumber] VARCHAR(30) NOT NULL, 
    [UpdateUtc] DATETIME NULL, 
    [UpdateLocal] DATETIME NULL, 
    [Latitude] DECIMAL(9,6) NULL, 
    [Longitude] DECIMAL(9,6) NULL, 
    [GeoLocation] AS geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) +')', 4326) PERSISTED, 
    [SpeedKm] DECIMAL(5, 2) NULL, 
    [DisplayState] VARCHAR(20) NULL, 
    [CreatedDateUtc] DATETIME NULL CONSTRAINT [DF_Location_Hold_CreatedDateUtc] DEFAULT SYSUTCDATETIME(),
    CONSTRAINT [PK_Location_Hold] PRIMARY KEY ([VehicleNumber])
)
GO

This is what was captured in the profiler trace:

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [VZ].[tmp_ms_xx_Location_Hold] (
    [VehicleNumber]  VARCHAR (30)   NOT NULL,
    [UpdateUtc]      DATETIME       NULL,
    [UpdateLocal]    DATETIME       NULL,
    [Latitude]       DECIMAL (9, 6) NULL,
    [Longitude]      DECIMAL (9, 6) NULL,
    [GeoLocation]    AS             geography::STPointFromText('POINT(' + CAST ([Longitude] AS VARCHAR (20)) + ' ' + CAST ([Latitude] AS VARCHAR (20)) + ')', 4326) PERSISTED,
    [SpeedKm]        DECIMAL (5, 2) NULL,
    [DisplayState]   VARCHAR (20)   NULL,
    [CreatedDateUtc] DATETIME       CONSTRAINT [DF_Location_Hold_CreatedDateUtc] DEFAULT SYSUTCDATETIME() NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_Location_Hold1] PRIMARY KEY CLUSTERED ([VehicleNumber] ASC)
);
IF EXISTS (SELECT TOP 1 1 
           FROM   [VZ].[Location_Hold])
    BEGIN
        INSERT INTO [VZ].[tmp_ms_xx_Location_Hold] ([VehicleNumber], [UpdateUtc], [UpdateLocal], [Latitude], [Longitude], [SpeedKm], [DisplayState], [CreatedDateUtc])
        SELECT   [VehicleNumber],
                 [UpdateUtc],
                 [UpdateLocal],
                 [Latitude],
                 [Longitude],
                 [SpeedKm],
                 [DisplayState],
                 [CreatedDateUtc]
        FROM     [VZ].[Location_Hold]
        ORDER BY [VehicleNumber] ASC;
    END
DROP TABLE [VZ].[Location_Hold];
EXECUTE sp_rename N'[VZ].[tmp_ms_xx_Location_Hold]', N'Location_Hold';
EXECUTE sp_rename N'[VZ].[tmp_ms_xx_constraint_PK_Location_Hold1]', N'PK_Location_Hold', N'OBJECT';
COMMIT TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

This table simply had the computed column dropped and then re-added:

Original table definition:

CREATE TABLE [CRM].[QuarterlyGoal]
(
    [QuarterlyGoalRecId]    INT             IDENTITY (1, 1) NOT NULL, 
    [StartDate]             DATE            NOT NULL, 
    [EndDate]               DATE            NOT NULL, 
    [UserId]                INT             NOT NULL, 
    [RevenueAmount]         DECIMAL(11, 2)  NOT NULL,
    [IsActiveQuarter]       AS
        (CASE WHEN (
            [StartDate] <= CAST(GETDATE() AS DATE) 
            AND [EndDate] >= CAST(GETDATE() AS DATE)) 
                THEN 1 
                ELSE 0 
        END),
    CONSTRAINT [PK_QuarterlyGoal] PRIMARY KEY CLUSTERED ([QuarterlyGoalRecId] ASC),
    CONSTRAINT [FK_QuarterlyGoal_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[Users] ([Id])
);

This is what was in the profiler trace:

ALTER TABLE [CRM].[QuarterlyGoal] DROP COLUMN [IsActiveQuarter];
...
ALTER TABLE [CRM].[QuarterlyGoal]
    ADD [IsActiveQuarter] AS (CASE WHEN ([StartDate] <= CAST (GETDATE() AS DATE)
                                         AND [EndDate] >= CAST (GETDATE() AS DATE)) THEN 1 ELSE 0 END);

Sqlpackage.exe is dropping default constraints and computed columns

0 Answers0