1

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:

enter image description here

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?

1 Answer 1

2

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

Screenshot of SSDT schema compare showing the columns will be reordered

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

Screenshot of Schema Compare Options window with "Ignore column order" checed

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:

screenshot of SSDT schema compare tool showing the constraint and column order changes

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

Screenshot of the generate script button, which looks like a piece of paper

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.';
2
  • I should have thought to generate the script and inspect it. It makes me nervous when the UI is not indicating what it is actually doing. I'm starting to think the Red Gate tools were worth the price of admission. May I ask what you use for comparing schema's and deployment of changes?
    – tnk479
    Jun 9, 2021 at 14:24
  • @tnk479 I haven't used the Red Gate tools, but I have heard that they are "worth the price of admission" from many folks who switched from SSDT. For deploying changes, I always generate the script (as part of a build process) and then review the T-SQL to make sure it's not doing any unexpected or unnecessary. Jun 9, 2021 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.