• SqlPackage or DacFx Version: 162.1.167.1
• .NET Framework (Windows-only) or .NET Core: .NET Core
• Environment (local platform and source/target platforms): Microsoft SQL Server 2019 (RTM-CU12) (KB5004524) - 15.0.4153.1 (X64) Jul 19 2021 15:37:34 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 22621: ) (Hypervisor)

Steps to Reproduce:

1. Create a table with a VARCHAR(MAX) column
2. Deploy the table
3. Change the column from VARCHAR(MAX) to VARCHAR(250)
4. Expected: sqlpackage emits an ALTER Column statement
5. Actual: sqlpackage emits a table rebuild

Change

CREATE TABLE TestTable(Col VARCHAR(MAX))

To

CREATE TABLE TestTable(Col VARCHAR(250))

Results in:

CREATE TABLE [dbo].[tmp_ms_xx_TestTable] (
    [Col] VARCHAR (250) NULL
);
IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[TestTable])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_TestTable] ([Col])
        SELECT [Col]
        FROM   [dbo].[TestTable];
    END
DROP TABLE [dbo].[TestTable];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_TestTable]', N'TestTable';

Why is this a problem? Table rebuilds are bad and often impossible to do on extremely large tables with high usage. For simple column table data changes, I expect sqlpackage to script simple ALTER statements.

Did this occur in prior versions? If not - which version(s) did it work in?

(DacFx/SqlPackage/SSMS/Azure Data Studio)