SQL Compare 5.2.0.32 Shows Tables Identical when they're not

DWolford1234

After moving changes to production, I had a developer come to me and said that data was inserted into the incorrect columns from Stage to Prod. My thought were that the column order must be different in Prod than it is in Stage. I ran a compare against the tables, but the one in question fell under the Identical Objects group. When I scrolled through the list and clicked on the table, the script of each do not line up. Now there are the same number and the same column names / definitions in each table, but the order of the last four are mixed up (Naturally, this is due to our developers having way to much access to prod, but that's another battle). My problem is that I have come to trust SQL Compare to show me any differences before and after a move to production, but the fact that this table didn't get flagged as having differences is very troubling. These Tables do have a Case Sensitive Collation, so I checked on the 'Treat Items as Case Sensitive' Behavior Option.
Again, Everything is identical except for the order of the columns. Shouldn't that show as a difference?


Here are the scripts of the tables (Columns only, everything else was identical):
-- Columns

CREATE TABLE [dbo].[S_CS_QUEST]
(
[ROW_ID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CREATED] [datetime] NOT NULL CONSTRAINT [DF__S_CS_QUES__CREAT__62B136C0] DEFAULT (getdate()),
[CREATED_BY] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LAST_UPD] [datetime] NOT NULL CONSTRAINT [DF__S_CS_QUES__LAST___63A55AF9] DEFAULT (getdate()),
[LAST_UPD_BY] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MODIFICATION_NUM] [numeric] (10, 0) NOT NULL CONSTRAINT [DF__S_CS_QUES__MODIF__64997F32] DEFAULT (0),
[CONFLICT_ID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__S_CS_QUES__CONFL__658DA36B] DEFAULT ('0'),
[ANSWR_TYPE_CD] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CHOICE_FLG] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NAME] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RPLCTN_LVL_CD] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__S_CS_QUES__RPLCT__6681C7A4] DEFAULT ('All'),
[ANS_TBL_FLG] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FONT_SIZE] [numeric] (10, 0) NULL,
[MAX_VAL] [numeric] (22, 7) NULL,
[MIN_VAL] [numeric] (22, 7) NULL,
[TEXT_AREA_HGHT] [numeric] (22, 7) NULL,
[TEXT_AREA_WDTH] [numeric] (22, 7) NULL,
[ANSWER_FORMAT_CD] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AUTO_SUBST_PARAMS] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRNCH_ANSWR_EXPR] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CURCY_CD] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CURCY_FIELD] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DET_APPLET_NAME] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DFLT_ANSWR_ID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FONT_FACE] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MUST_ANSWER_CD] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MVG_APPLET_NAME] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PICK_APPLET_NAME] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAVE_BUSCOMP] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAVE_BUSOBJ] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAVE_FIELD] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAVE_USER_PARAMS] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SEARCH_SPEC] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STYLE_ID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TEXT_COLOR] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TYPE_CD] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[X_CNX_NAME] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[X_CNX_BUSRULE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[X_CNX_BI_TEXT] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[X_CNX_BI_CATEGORY] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

)

GO

---

vs.

---

-- Columns

CREATE TABLE [dbo].[S_CS_QUEST]
(
[ROW_ID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CREATED] [datetime] NOT NULL CONSTRAINT [DF__S_CS_QUES__CREAT__62B136C0] DEFAULT (getdate()),
[CREATED_BY] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LAST_UPD] [datetime] NOT NULL CONSTRAINT [DF__S_CS_QUES__LAST___63A55AF9] DEFAULT (getdate()),
[LAST_UPD_BY] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MODIFICATION_NUM] [numeric] (10, 0) NOT NULL CONSTRAINT [DF__S_CS_QUES__MODIF__64997F32] DEFAULT (0),
[CONFLICT_ID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__S_CS_QUES__CONFL__658DA36B] DEFAULT ('0'),
[ANSWR_TYPE_CD] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CHOICE_FLG] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NAME] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RPLCTN_LVL_CD] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__S_CS_QUES__RPLCT__6681C7A4] DEFAULT ('All'),
[ANS_TBL_FLG] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FONT_SIZE] [numeric] (10, 0) NULL,
[MAX_VAL] [numeric] (22, 7) NULL,
[MIN_VAL] [numeric] (22, 7) NULL,
[TEXT_AREA_HGHT] [numeric] (22, 7) NULL,
[TEXT_AREA_WDTH] [numeric] (22, 7) NULL,
[ANSWER_FORMAT_CD] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AUTO_SUBST_PARAMS] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BRNCH_ANSWR_EXPR] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CURCY_CD] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CURCY_FIELD] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DET_APPLET_NAME] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DFLT_ANSWR_ID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FONT_FACE] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MUST_ANSWER_CD] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MVG_APPLET_NAME] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PICK_APPLET_NAME] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAVE_BUSCOMP] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAVE_BUSOBJ] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAVE_FIELD] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SAVE_USER_PARAMS] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SEARCH_SPEC] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STYLE_ID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TEXT_COLOR] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TYPE_CD] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[X_CNX_BI_CATEGORY] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[X_CNX_BI_TEXT] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[X_CNX_NAME] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[X_CNX_BUSRULE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

GO

---

Thank You,
Dan

medwards

In Edit Project on the Options tab, go to the "more options" screen and select "Force column order" under Behavior

DWolford1234

That worked, Thanks.

Of course, I understand that properly specifying your columns on an insert operation will avoid any issues that may arrise from the columns being in a different order, but it still seems like it should be a more basic/default option...to me anyway.

Anyway, thanks for the answer medwards!