Apparently odd comparison behavior in 3.1.0.151

dgregoire

I am seeing some odd behavior in a generated synchronization script
I generated with SQL Compare

1) create databases as using the script below

2) compare with default options

3) make a sync script to change database test1 match database test2

4) the data migration select/insert statement is incorrect

INSERT INTO [dbo].[tmp_rg_xx_stm_ItemBank]([iItemBankID],
[iGradeLevelID], [iContentAreaID], [iLanguageID],
[iSubjectSetID], [vchTitle], [chItemBankType],
[iNumberScanQuestions], [iNumberRubricQuestions],
[iNumberAddOns], [flRelease], [iInsertByID],
[dtInsertDate], [iEditByID], [dtEditDate], [tiRowStatus])
SELECT [iItemBankID], [iGradeLevelID], [iContentAreaID],
[iLanguageID], [iSubjectSetID], [vchTitle], [chItemBankType],

-- selects iNumberRubricQuestions twice from the source table,
-- the way these tables are structured, this should not work
-- since iNumberScanQuestions is a new field with no default
-- or source of data in the original table

[iNumberRubricQuestions], [iNumberRubricQuestions], [iNumberAddOns],
[flRelease], [iInsertByID], [dtInsertDate], [iEditByID],
[dtEditDate], [tiRowStatus] FROM [dbo].[stm_ItemBank]


---
---
---test script
---






use master
if EXISTS (select * from master.dbo.sysdatabases where name = 'test1')
drop database test1
go
create database test1
go

if EXISTS (select * from master.dbo.sysdatabases where name = 'test2')
drop database test2
go
create database test2
go
use test1
go


CREATE DEFAULT dtEditDate AS GETDATE()
go

CREATE DEFAULT dtInsertDate AS GETDATE()
go

CREATE DEFAULT iEditByID AS 1
go

CREATE DEFAULT iInsertByID AS 1
go

CREATE DEFAULT iPassingPercentage AS 75
go

CREATE DEFAULT NewGUID AS NEWID()
go

CREATE DEFAULT tiFalseFlag AS 0
go

CREATE DEFAULT tiRowStatus AS 1
go

CREATE DEFAULT tiTrueFlag AS 1
go


-- Columns
CREATE TABLE [dbo].[stm_ItemBank]
(
[iItemBankID] [int] NOT NULL IDENTITY(1, 1),
[iGradeLevelID] [int] NOT NULL,
[iContentAreaID] [int] NOT NULL,
[iLanguageID] [int] NOT NULL,
[iSubjectSetID] [int] NULL,
[iItemBankCode] [int] NOT NULL,
[vchTitle] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[chItemBankType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[iNumberMCQuestions] [int] NOT NULL,

[iNumberRubricQuestions] [int] NOT NULL,
[iNumberAddOns] [int] NOT NULL,
[flRelease] [float] NOT NULL,


[iInsertByID] [int] NOT NULL,
[dtInsertDate] [datetime] NOT NULL,
[iEditByID] [int] NOT NULL,
[dtEditDate] [datetime] NOT NULL,
[tiRowStatus] [tinyint] NOT NULL
)

GO

-- Constraints and indexes
ALTER TABLE [dbo].[stm_ItemBank] ADD CONSTRAINT [PK_iItemBankID] PRIMARY KEY CLUSTERED ([iItemBankID])
GO




CREATE NONCLUSTERED INDEX [IX_chItemBankType] ON [dbo].[stm_ItemBank] ([chItemBankType], [iGradeLevelID], [iContentAreaID])
GO
CREATE NONCLUSTERED INDEX [IX_iContentAreaID] ON [dbo].[stm_ItemBank] ([iContentAreaID], [iGradeLevelID])
GO
CREATE NONCLUSTERED INDEX [IX_iGradeLevelID] ON [dbo].[stm_ItemBank] ([iGradeLevelID], [iContentAreaID])
GO
CREATE NONCLUSTERED INDEX [IX_vchTitle] ON [dbo].[stm_ItemBank] ([vchTitle])
GO











-- Bindings
sp_bindefault N'iInsertByID', N'[dbo].[stm_ItemBank].[iInsertByID]'
GO
sp_bindefault N'dtInsertDate', N'[dbo].[stm_ItemBank].[dtInsertDate]'
GO
sp_bindefault N'iEditByID', N'[dbo].[stm_ItemBank].[iEditByID]'
GO
sp_bindefault N'dtEditDate', N'[dbo].[stm_ItemBank].[dtEditDate]'
GO



use test2
go



CREATE DEFAULT dtEditDate AS GETDATE()
go

CREATE DEFAULT dtInsertDate AS GETDATE()
go

CREATE DEFAULT iEditByID AS 1
go

CREATE DEFAULT iInsertByID AS 1
go

CREATE DEFAULT iPassingPercentage AS 75
go

CREATE DEFAULT NewGUID AS NEWID()
go

CREATE DEFAULT tiFalseFlag AS 0
go

CREATE DEFAULT tiRowStatus AS 1
go

CREATE DEFAULT tiTrueFlag AS 1
go

-- Columns
CREATE TABLE [dbo].[stm_ItemBank]
(
[iItemBankID] [int] NOT NULL IDENTITY(1, 1),
[iGradeLevelID] [int] NOT NULL,
[iContentAreaID] [int] NOT NULL,
[iLanguageID] [int] NOT NULL,
[iSubjectSetID] [int] NULL,
[vchItemBankCode] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[vchTitle] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[chItemBankType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__stm_ItemB__chIte__261B931E] DEFAULT ('S'),
[iAuthorID] [int] NULL,
[iNumberScanQuestions] [int] NOT NULL,
[iNumberRubricQuestions] [int] NOT NULL,
[iNumberAddOns] [int] NOT NULL CONSTRAINT [DF__stm_ItemB__iNumb__2803DB90] DEFAULT (0),
[flRelease] [float] NOT NULL CONSTRAINT [DF__stm_ItemB__flRel__28F7FFC9] DEFAULT (0.0),
[tiExpired] [tinyint] NOT NULL CONSTRAINT [DF__stm_ItemB__tiExp__29EC2402] DEFAULT (0),
[tiLocked] [tinyint] NOT NULL CONSTRAINT [DF__stm_ItemB__tiLoc__2AE0483B] DEFAULT (0),
[iInsertByID] [int] NOT NULL,
[dtInsertDate] [datetime] NOT NULL,
[iEditByID] [int] NOT NULL,
[dtEditDate] [datetime] NOT NULL,
[tiRowStatus] [tinyint] NOT NULL CONSTRAINT [DF__stm_ItemB__tiRow__2BD46C74] DEFAULT (1)
)

GO

-- Constraints and indexes
ALTER TABLE [dbo].[stm_ItemBank] ADD CONSTRAINT [CK__stm_ItemB__chIte__19EAC663] CHECK (([chItemBankType] = 'S' or [chItemBankType] = 'D'))
GO
ALTER TABLE [dbo].[stm_ItemBank] ADD CONSTRAINT [CK__stm_ItemB__tiRow__1DBB5747] CHECK (([tiRowStatus] = 1 or [tiRowStatus] = 0))
GO
ALTER TABLE [dbo].[stm_ItemBank] ADD CONSTRAINT [PK_iItemBankID] PRIMARY KEY CLUSTERED ([iItemBankID])
GO
CREATE NONCLUSTERED INDEX [IX_chItemBankType] ON [dbo].[stm_ItemBank] ([chItemBankType], [iGradeLevelID], [iContentAreaID])
GO
CREATE NONCLUSTERED INDEX [IX_iContentAreaID] ON [dbo].[stm_ItemBank] ([iContentAreaID], [iGradeLevelID])
GO
CREATE NONCLUSTERED INDEX [IX_iGradeLevelID] ON [dbo].[stm_ItemBank] ([iGradeLevelID], [iContentAreaID])
GO
CREATE NONCLUSTERED INDEX [IX_vchTitle] ON [dbo].[stm_ItemBank] ([vchTitle])
GO

-- Foreign keys


-- Bindings
sp_bindefault N'iInsertByID', N'[dbo].[stm_ItemBank].[iInsertByID]'
GO
sp_bindefault N'dtInsertDate', N'[dbo].[stm_ItemBank].[dtInsertDate]'
GO
sp_bindefault N'iEditByID', N'[dbo].[stm_ItemBank].[iEditByID]'
GO
sp_bindefault N'dtEditDate', N'[dbo].[stm_ItemBank].[dtEditDate]'
GO

Brian Donahue

Hi,

In this case it looks like SQL Compare is inserting the data bacause it thinks that the iNumberRubricQuestions column is being renamed to iNumberScanQuestions and it's trying to preserve the data.

SQL Compare looks at the column names if a table is being rebuilt, and based on a regular expression of the names of columns that are being dropped and added, makes a best-guess that the column is a rename and inserts data into it from the column being dropped.

Normally this causes no real harm aside from the query taking longer to execute.