SSMS 22.7.1 Schema Compare: Five More Reasons to Look Before You Apply

In my earlier post, SSMS 22.7 Schema Compare: Before You Click Apply, I explored some of the things that can happen when you trust Schema Compare to make changes without looking closely at what it's about to do.

Then Thomas Franz left a comment with five more scenarios he thought were worth testing. And they were good ones!

So, first, a big thank-you to Thomas for taking the time to share his ideas. This is one of my favorite things about technical blogging. Someone reads a post, brings another perspective, and suddenly there's more to investigate.

Back to the sandbox! This time I'm using SSMS 22.7.1, with separate source and target databases.

1. Renaming a Table

I started with two identical tables containing the same three rows. Then I renamed the source table from CustomerOrders to SalesOrders.

Schema Compare reported two differences: one Delete and one Add.

It didn't recognize the rename. The generated deployment script proposed dropping CustomerOrders and creating SalesOrders.

Fortunately, the generated script included a check intended to stop deployment if the original table contained data. But it was still proposing to drop and recreate a table that only needed a new name.

My takeaway: A simple rename isn't necessarily a simple deployment. Check the generated script before assuming it's just a rename.

2. System-Generated Constraint Names

Next, I created identical tables in both databases, allowing SQL Server to generate the primary key and default constraint names.

The primary key names were different, as expected. The default constraint names happened to be identical.

Schema Compare didn't report any additional differences for the table.

That was a pleasant result. At least in this test, different system-generated primary key names didn't produce unnecessary deployment changes.

3. Changing a Computed Column

For this test, both databases contained a table with a persisted computed column named Total. The source calculated Quantity * UnitPrice, while the target applied a 10% discount.

-- source
Total AS (Quantity * UnitPrice) PERSISTED
-- target
Total AS (Quantity * UnitPrice * 0.90) PERSISTED

Schema Compare correctly identified the difference as a Change.

Here's the relevant portion of its generated script:

ALTER TABLE [dbo].[ComputedColumnTest]
    DROP COLUMN [Total];

ALTER TABLE [dbo].[ComputedColumnTest]
    ADD [Total] AS ([Quantity] * [UnitPrice]) PERSISTED;

No table replacement was scripted. The computed column would be dropped and recreated, with its persisted values recalculated from the existing columns.

Of course, dependencies on that computed column could complicate things. My test table was deliberately simple.

My takeaway: Schema Compare handled this change reasonably, but DROP/ADD still deserves a look when dependencies are involved.

4. Same Columns, Different Order

This one got my attention. I created two tables with identical column names, data types, and nullability. The only difference was their order.

Schema Compare reported a Change, but in the generated script it proposed rebuilding the entire table. It would create a temporary table with the desired column order, copy the data, drop the original table, and rename the temporary table.

All that work just for different column order.

The good news is that Schema Compare has an Ignore Column Order option. I enabled it, ran the comparison again, and the difference disappeared.

My takeaway: Consider checking 'Ignore Column Order' in the Schema Compare options before allowing a cosmetic difference to trigger an unnecessary table rebuild.

5. Does Capitalization Matter?

For the final test, Thomas suggested comparing identifiers with different casing. I created two tables, one with a column named CustomerID and the other with customerid.

First, I used databases with the case-insensitive collation SQL_Latin1_General_CP1_CI_AS, and no differences were reported.

Then I repeated the test using two new databases with the case-sensitive collation SQL_Latin1_General_CP1_CS_AS. This time, Schema Compare reported a Change.

But here's the interesting part. Rather than generating a column rename for the difference in capitalization, it produced a DROP/ADD:

ALTER TABLE [dbo].[CaseTest]
    DROP COLUMN [customerid];

ALTER TABLE [dbo].[CaseTest]
    ADD [CustomerID] INT NOT NULL;

The generated script also warned about potential data loss and included this check to prevent deployment against a populated table.

IF EXISTS (SELECT TOP 1 1 FROM [dbo].[CaseTest])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT;

My takeaway: Collation matters, and even a case change can result in a potentially destructive deployment script.

Where I Landed

Here's how the five tests turned out:

Test	What SSMS 22.7.1 Did
Table rename	Proposed DROP/CREATE rather than a rename
Generated constraint names	Ignored different system-generated primary key names
Computed column	Proposed DROP/ADD of the computed column
Column order	Proposed a table rebuild; Ignore Column Order eliminated it
Identifier casing	Ignored under CI collation; proposed DROP/ADD under CS collation

Some results were reassuring. Others were exactly why I started looking more closely at Schema Compare in the first place.

These tests don't cover every possible dependency or deployment option, but they show why reviewing the proposed changes before running the scripts is important.

Very simply, Schema Compare can identify a difference correctly and still generate a deployment script you wouldn't want to run.

And once again, thanks to Thomas Franz for the suggestions. Five more tests, a few surprises, and another reminder that some of the best investigations begin in the comments.

Before you click Apply, be sure to look at what you're actually applying!

More to Read

sqlfingers — SSMS 22.7 Schema Compare — Before You Click Apply
Microsoft Learn: Schema comparison overview