Table rebuilds when changing column type from VARCHAR(MAX)

[asrichesson]

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

[asrichesson]

Changing the scale on a decimal column also causes a table rebuild. Change

Create Table TestTable(Col1 DECIMAL(11,4))

To

Create Table TestTable(Col1 DECIMAL(11,5))

[namangupta211]

Thanks for reaching out to us. Yes, there are cases when table gets rebuild because of small, sometimes unnecessary changes. This is an existing problem and Sqlpackage team is working on it. Meanwhile you can use a publish option, /p: AllowTableRecreation, which specifies whether to allow table recreation during deployment if required to perform the schema change. If you set it to False, then the deployment will halt and let you know which tables are getting recreated.

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver16