sqlpackage migration script always wants to rebuild

Question

Can anyone explain when using sqlpackage.exe to produce a migration script always wants to drop the table and recreate when adding a field.

My databases are hosted on Azure PaaS service.

I have a database with a table created with the following sql:

CREATE TABLE [dbo].[test] (
    [Id]             INT            IDENTITY (1, 1) NOT NULL,
    [Field 1]        INT            NULL,
    [Timestamp]      ROWVERSION     NOT NULL,
    [InsertedTime]   DATETIME       DEFAULT (getdate()) NOT NULL,
    [UpdatedTime]    DATETIME       NULL,
    [LastUpdatedBy]  NVARCHAR (100) NULL,
    [AssignedTo]     NVARCHAR (90)  NULL,
    [ActionRequired] NVARCHAR (MAX) NULL,
    [AuditLog]       XML            NULL,
    PRIMARY KEY NONCLUSTERED ([Id] ASC)
);

I then create a new temporary database and run the following sql (adding field 2)

CREATE TABLE [dbo].[test] (
    [Id]             INT            IDENTITY (1, 1) NOT NULL,
    [Field 1]        INT            NULL,
    [Field 2]        INT            NULL,
    [Timestamp]      ROWVERSION     NOT NULL,
    [InsertedTime]   DATETIME       DEFAULT (getdate()) NOT NULL,
    [UpdatedTime]    DATETIME       NULL,
    [LastUpdatedBy]  NVARCHAR (100) NULL,
    [AssignedTo]     NVARCHAR (90)  NULL,
    [ActionRequired] NVARCHAR (MAX) NULL,
    [AuditLog]       XML            NULL,
    PRIMARY KEY NONCLUSTERED ([Id] ASC)
);

Then run the following command to extract the dacpac:

sqlpackage.exe /SourceConnectionString:"<tempdb>" /a:Extract /tf:local.dacpac

Then run this to get a migration report:

sqlpackage.exe /TargetConnectionString:"<orginaldb>" /a:DeployReport /sourcefile:local.dacpac /outputpath:report.xml

But it always wants to do this:

<?xml version="1.0" encoding="utf-8"?>
<DeploymentReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DeployReport/2012/02">
    <Alerts>
        <Alert Name="DataMotion">
            <Issue Value="[dbo].[test]" />
        </Alert>
    </Alerts>
    <Operations>
        <Operation Name="TableRebuild">
            <Item Value="[dbo].[test]" Type="SqlTable" />
        </Operation>
    </Operations>
</DeploymentReport>

Answer 1

The behavior of sqlpackage.exe can be influenced very detailed by many parameters, here are only a few shown:

A detailed list of all parameters you can find here:
https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-deploy-drift-report?view=sql-server-ver15

It takes likely some time and thoughts to adjust it exactly to your needs.

Answer 2

This probably happens because the new column is not the last column in the table (it is before other existing columns). In such a case also the SSMS Table Designer needs to recreate the table (to preserve the specified order of columns).