Sql server string comparison problem with chinese white-space

Question

I have a record in my sql server database with the following value for a nvarchar column: '穂 坂' The second character is the Unicode value 0x20 which is a simple space character.

There is a unique key constraint on that column.

I get a unique key violation when I try to insert the following value: '穂　坂' In this string, the second character is the Unicode value 0x3000, which is a chinese space character.

Why do I get the unique key violation? Why does sql server "convert" the chinese white-space character to a simple space?

Thanks in advance for any insights! My collation is SQL_Latin1_General_CP1_CI_AS by the way.

Answer 1

It could be that your collation is width-insensitive.

Consider:

• changing your database's collation to one that fits your needs, and it width-sensitive.
• change the collation on that particular column to one that is width-sensitive. This could/will lead to more COLLATE clauses throughout your stored procs, etc. Not sure if this is a recommended practice by DBAs.