1

I am using RedGate SQL Compare 11.6 to compare two SQLServer 2016 databases.

The problem is, the comparison result includes tables that have same columns but in different order. I would like to exclude this. Is there a way to do these?

RedGate's Support forum and help documents say that 'Default behavior is to ignore such differences' But it doesn't seem to work like that.

Devasayal
  • 40
  • 8

1 Answers1

2

By default it doesn't compare column order. To validate this, I set up a test this way using SQL Server 2016 & Compare 11.6:

USE Test;
GO
CREATE TABLE dbo.Order1 (
     ID INT,
     NotID TINYINT,
     SomeValue VARCHAR(50)
    );
GO

USE Test2;
GO
CREATE TABLE dbo.Order1 (
     SomeValue VARCHAR(50),
     ID INT,
     NotID TINYINT
    );
GO

When I ran compare against the two tables, the output was here: enter image description here

If you note at the top, it says there are 24 identical objects. These two tables count as identical. However, compare does note that the column order is different. There's an option you can set "Force Column Order" that changes this behavior. When that's enabled you would see this from the comparison: enter image description here

It's possible that's what you're seeing. Check the options on the Compare project.

Grant Fritchey
  • 2,645
  • 19
  • 21
  • That's a good point! I do have other differences in the table ( like missing indexes, constraints). That could be the reason why it was not under identical objects. Nevertheless, My primary concern was If you click "show" -> groups, it shows that Columns are not equal, even if only the order is differing. – Devasayal Aug 31 '16 at 09:23
  • That's because they are different. However, it doesn't show up in the listing of tables with differences unless you have Force Order selected, and it won't be selectable for updating unless you've done that, and it won't change the order of the columns in the T-SQL it generates unless you've done that. It's just displaying the differences that it sees. – Grant Fritchey Sep 02 '16 at 10:09