Unique SQL Server Constraint Problem for Unicode Characters

Question

I have a table definition as follows:

create table [Language](
Id int primary key identity,
Code varchar(11) not null unique,
NativeName nvarchar(50) not null unique
)

And then I have a long list of statements that are inserted into this table. The problem is that some of the insert statements conflict with my unique column constraint NativeName. The strange thing is that the content is not unique at all. For example, if I only insert the following with an empty table:

insert into Language (Code, NativeName) values('am', N'አማርኛ');
insert into Language (Code, NativeName) values('dv', N'ދިވެހިބަސް‏');

I get the second insert.

Violation of UNIQUE KEY constraint 'UQ__Language__EB1957A5F98D1F9C'. Cannot insert duplicate key in object 'dbo.Language'. The duplicate key value is (ދިވެހިބަސް‏).

Does anyone know why Unicode characters cause these problems?

Answer 1

Try declaring a NativeName column with more specific (binary) sorting.

eg:

 NativeName nvarchar(50) collate SQL_Latin1_General_CP437_BIN not null unique