Compare case now insensitive?

Bernard Sheppard

I have a table two columns varchar(10):

CREATE TABLE [tbProfileArea2Lnsp] (
	[ProfileArea] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
	[LNSP] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
	CONSTRAINT [pkProfileArea2Lnsp] PRIMARY KEY  CLUSTERED 
	(
		[ProfileArea],
		[LNSP]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

In Dev, some of the fields are lowercase:

SELECT  ProfileArea
FROM    tbProfileArea2LNSP 
WHERE   ASCII(LEFT(UPPER(ProfileArea), 1)) <> ASCII(LEFT(ProfileArea, 1))

which returns:

citipower
ergon1
integral

The same in SysTest returns nothing - they're upper case.

The previous version of Data Compare always selected these rows as different, but never generated a script to change from upper to lower or vice versa.

It was good that it detected the difference, but not so good that it couldn't script the update.

The current version doesn't detect the difference at all.

richardjm

A lot of work has been performed on this version of data compare for better Collation compliance. In your table your schema says that the collation is case insensitive therefore there is no difference in the data from one to the other according to your own schema.

If in one database you have
"Data"
and in the other database
"data"
the two strings are the same according to the collation. If you wish for the data to be compared as different you need to set a case sensitive collation.

Hope this helps.
Richard Mitchell

Bernard Sheppard

richardjm wrote:

A lot of work has been performed on this version of data compare for better Collation compliance. In your table your schema says that the collation is case insensitive therefore there is no difference in the data from one to the other according to your own schema.

If in one database you have
"Data"
and in the other database
"data"
the two strings are the same according to the collation. If you wish for the data to be compared as different you need to set a case sensitive collation.

Hope this helps.
Richard Mitchell

Hi Richard, that makes sense, and I'm happy with the approach you've taken, and the results that it generates in this scenario - now that I understand it.

We have a few tables where we have things like codes & descriptions. We want to be able to edit a description to cORRECT tHINGS lIKE aCCIDENTAL cAPS lOCK, and have that change detected and propagated - that means we should make those columns Case Sensitive.

richardjm

That is exactly correct. For there to be a difference in the columns in the database the column must be made case-sensitive. There is the proviso that if the collations in the two databases differ we use the collation from the left hand database ( database1 in the API ) as there is no sensible way to compare two different collations together. This may in some circumstances produce strange results but as SQL Server doesn't allow this at all it's still
of benefit.

There will probably be an option introduced in a future version of the product that will force binary collation comparisons. This will mean that indexes can't be used but at least the result of the comparison will be predictable.