Visual Studio Data Tools (SSDT) schema comparison tool seems like it's not working correctly

All we need is an easy explanation of the problem, so here it is.

I recently started using the SSDT schema and data and comparison tools. For years I was using Red Gate’s SQL Compare and SQL Data Compare, but I figured the VS tools are probably enough and figured I would see if I could stand to live with them. They’ve been working fine for the past couple of months, but today I’m getting a bizarre result when comparing the schema between a local database and an Azure SQL database that I want to deploy changes to for a web app. The schema comparison has marked several columns as different and indicating that they will be deleted. There is in fact, one new column being added. However, nothing has changed in any of the other columns. I ticked the "Ignore Column Order" box so that column order is not considered. I also unchecked users and roles because they are different between my local dev and the staging environment and I don’t want to consider those differences.

Here’s what it looks like:

Visual Studio Data Tools (SSDT) schema comparison tool seems like it's not working correctly

Is the coloring just wrong? Will it actually delete the PermitLimit column and recreate it even though nothing has changed?? Is there some other setting I need to set so that these identical columns are not being highlighted as having differences?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The schema compare UI gets a little mixed up in the face of multiple changes like this sometimes. You should use the "Generate Script" button to see what actual changes will be made at runtime. Generally, you want your test run to be as similar as possible to how you’ll really deploy the changes.

In your case, the combination that’s causing confusion is:

  • some columns are being reordered
  • some default constraints are being converted to named constraints

I can repeat your problem by creating this table and deploying it locally:

CREATE TABLE dbo.TableA
(
    Id int NOT NULL,
    ABitColumn bit NOT NULL DEFAULT ((1)),
    AStringColumn varchar(10) NOT NULL,
    AnIntColumn int NULL,

    CONSTRAINT PK_TableA PRIMARY KEY (Id)
)

Now, I can alter the source file to reorder the int and string columns, and the schema compare tool will show that change by default:

CREATE TABLE dbo.TableA
(
    Id int NOT NULL,
    ABitColumn bit NOT NULL DEFAULT ((1)),
    AnIntColumn int NULL,
    AStringColumn varchar(10) NOT NULL,

    CONSTRAINT PK_TableA PRIMARY KEY (Id)
)

Visual Studio Data Tools (SSDT) schema comparison tool seems like it's not working correctly

As you mentioned, I can enable the "Ignore column order" setting using the little gear icon, and schema compare then shows no changes.

Visual Studio Data Tools (SSDT) schema comparison tool seems like it's not working correctly

If I then go and give my default constraint a name:

CREATE TABLE dbo.TableA
(
    Id int NOT NULL,
    ABitColumn bit NOT NULL CONSTRAINT DF_TableA_ABitColumn DEFAULT ((1)),
    AnIntColumn int NULL,
    AStringColumn varchar(10) NOT NULL,

    CONSTRAINT PK_TableA PRIMARY KEY (Id)
)

The schema compare shows the column order change and the constraint name change, even though I still have "ignore column order" checked:

Visual Studio Data Tools (SSDT) schema comparison tool seems like it's not working correctly

The more reliable way to see what changes will actually be made is to click the "generate script" button in the toolbar:

Visual Studio Data Tools (SSDT) schema comparison tool seems like it's not working correctly

The script produced does not drop and re-add any columns:

USE [$(DatabaseName)];


GO
PRINT N'Dropping Default Constraint unnamed constraint on [dbo].[TableA]...';


GO
ALTER TABLE [dbo].[TableA] DROP CONSTRAINT [DF__TableA__ABitColu__24927208];


GO
PRINT N'Creating Default Constraint [dbo].[DF_TableA_ABitColumn]...';


GO
ALTER TABLE [dbo].[TableA]
    ADD CONSTRAINT [DF_TableA_ABitColumn] DEFAULT ((1)) FOR [ABitColumn];


GO
PRINT N'Update complete.';

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply