SSDT Adding 2 columns to the end of a table leads to table rebuild

Question

We are using Azure DevOps to automatically deploy changes to our production database, which is an Azure SQL database. This runs fine for most releases, as we do limited changes most of the time. What does give us problems sometimes, is table rebuilds which take a lot of time to complete. The end result is fine, only the long running time leads to website downtime which we can ill afford.

We are using a database project in Visual Studio to manage this and some other databases. This includes a xml file which we include in publishing with SSDT which contains the settings of the deployment process. (Ie ignore column order and the likes).

The following change leads to a table rebuild of the table:

CREATE TABLE [dbo].[AccountCompany] (
    [CompanyId]                   INT             IDENTITY (1, 1) NOT NULL,
    [AccountTypeCode]             NVARCHAR (2)    DEFAULT ('00') NOT NULL,
    <Various columns, some with defaults>
    [PONumberMandatoryUpstream] BIT NULL, 
    [PONumberMandatoryDownstream] BIT NULL, 
    CONSTRAINT [PK_AccountCompany] PRIMARY KEY CLUSTERED ([CompanyId] ASC),
    CONSTRAINT [FK_AccountCompany_AccountBranchType] FOREIGN KEY ([BranchTypeCode]) REFERENCES [dbo].[AccountBranchType] ([BranchTypeCode]),
    CONSTRAINT [FK_AccountCompany_AccountCustomerBusinessSegment] FOREIGN KEY ([BusinessSegment]) REFERENCES [dbo].[AccountCustomerBusinessSegment] ([BusinessSegmentCode]),
    CONSTRAINT [FK_AccountCompany_AccountDmsEndpoint] FOREIGN KEY ([DmsEndpointId]) REFERENCES [dbo].[AccountDmsEndpoint] ([DmsEndpointId]),
    CONSTRAINT [FK_AccountCompany_AccountDmsType] FOREIGN KEY ([DmsTypeCode]) REFERENCES [dbo].[AccountDmsType] ([DmsTypeCode]),    
    CONSTRAINT [FK_AccountCompany_AccountType] FOREIGN KEY ([AccountTypeCode]) REFERENCES [dbo].[AccountType] ([AccountTypeCode]),
    CONSTRAINT [FK_AccountCompany_Currency_Purchase] FOREIGN KEY ([PurchaseCurrencyCode]) REFERENCES [dbo].[Currency] ([CurrencyCode]),
    CONSTRAINT [FK_AccountCompany_Currency_Sales] FOREIGN KEY ([SalesCurrencyCode]) REFERENCES [dbo].[Currency] ([CurrencyCode]),
    CONSTRAINT [FK_AccountCompany_MasterLanguage] FOREIGN KEY ([CultureCode]) REFERENCES [dbo].[MasterLanguage] ([CultureCode])
);

GO
CREATE NONCLUSTERED INDEX [IX_AccountCompany_LocationCode_Active]
    ON [dbo].[AccountCompany]([LocationCode] ASC, [Active] ASC)
    INCLUDE([AccountTypeCode], [BranchTypeCode], [DeliveryTimeDealer], [DeliveryTimeDealerGroup], [DeliveryTimeDealerPreferred], [DeliveryTimeFacingPdc], [DeliveryTimeTotalPaccar], [DmsDealerId], [DmsEndpointId], [DmsTypeCode], [FleetCustomerCode], [Guid], [CultureCode], [LogoAssetSequential], [LogoUrl], [Name], [PurchaseCurrencyCode], [RowVersion], [RushOrder], [SalesCurrencyCode]);

GO
CREATE NONCLUSTERED INDEX [IX_AccountCompany_Guid]
    ON [dbo].[AccountCompany]([Guid] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_AccountCompany_DmsTypeCode]
    ON [dbo].[AccountCompany]([DmsTypeCode] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_AccountCompany_AccountTypeCode]
    ON [dbo].[AccountCompany]([AccountTypeCode] ASC);

The last 2 columns, PONumberMandatoryUpstream and PONumberMandatoryDownstream, probably lead to a table rebuild. Both columns are new and are added to the end of the table, with no foreign keys/constraints/indexes. If I manually do a comparison with the schema compare, I see just an ALTER TABLE ADD COLUMN statement which does not rebuild the table. For some reason the automatic deployment decides the table needs rebuilding.

Anyone with an idea why SSDT thinks the table needs to be rebuild?

My first guess it might have something to do with the settings in the publish xml file. I tried comparing these settings with the ones in the schema compare, but there seem to be no differences. If needed I can include the deployment log and publish settings file.

Answer 1

I figured out what the issue was after 2 days of debugging. Turns out one of our admins enabled database discovery and classification https://learn.microsoft.com/en-us/azure/sql-database/sql-database-data-discovery-and-classification?tabs=azure-t-sql. This adds some extra SQL statements regarding specific columns and classification in the table definition, which were not in the database project. Every deployment these tables were rebuild, which in our case were 44 tables.

This classification structure is pretty new and ssdt deployment can't yet handle this in a pipeline. Adding these statements to the database project didn't work, as that then gave errors in the pipeline like unknown statement. Our only option was to disable classification until this is supported by ssdt.