Search code examples
redgatered-gate-data-compare

Redgate SQL Compare - How to ignore differences of Column order?

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.

Solution

  • 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.