Is this an issue with collation? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Is this an issue with collation?

Author

Topic

nihcas
Starting Member

1 Post

Posted - 2008-05-20 : 09:25:37

I'm extracting data from a source column with datatype varchar(80) and loading into a target column of data type nvarchar(80). Both the source column and target column have the same collation SQL_Latin1_General_CP1_CI_AS.

The target column has a unique constraint which fails during the data load operation.Upon investigation I found that the two strings below are considered the same in the target column of datatype nvarchar(80) :

'Malteser Schloßschule'
'Malteser Schlossschule'

I've tried to repro it using the T-SQL statements below, but can't understand why the second ALTER DDL fails.

CREATE TABLE SourceTable (name varchar(80) COLLATE SQL_Latin1_General_CP1_CI_AS)

INSERT INTO SourceTable values ('Malteser Schloßschule')
INSERT INTO SourceTable values ('Malteser Schlossschule')

CREATE TABLE TargetTable (name nvarchar(160) COLLATE SQL_Latin1_General_CP1_CI_AS)

INSERT INTO TargetTable
SELECT name FROM SourceTable

ALTER TABLE SourceTable ADD CONSTRAINT UIXTemp UNIQUE (name)
--Above DDL statement succeeds

ALTER TABLE TargetTable ADD CONSTRAINT UIXTemp1 UNIQUE (name)
-- Above DDL Fails with the error message below :
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.TEMP1' and the index name 'UIXTemp1'. The duplicate key value is (Malteser Schlossschule).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

For some reason in the TargetTable the strings 'Malteser Schloßschule' ,'Malteser Schlossschule' are the same. Using the similar T-SQL to query the SourceTable and TargetTable returns two different results while I expected the results to be same .

SELECT * FROM SourceTable WHERE name ='Malteser Schlossschule'

-- Results
'Malteser Schlossschule'

SELECT * FROM TargetTable WHERE name ='Malteser Schlossschule'

--Results
'Malteser Schloßschule'
'Malteser Schlossschule'

DROP TABLE SourceTable
DROP TABLE TargetTable

Can you please help me understand why the second ALTER DDL fails and why the second SELECT returns two rows?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 09:31:47

I think it is a UNICODE thingy. VARCHAR and NVARCHAR is not the same.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources