Sql server string comparison problem with chinese white-space

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, th开发者_运维技巧e 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.

First you may use the approriate string type in SQL including the collation. Second you may use UNICODE strings beginning by N prefix.

Example:

SELECT CAST(N'穂 坂' COLLATE Chinese_Simplified_Pinyin_100_BIN2 AS VARBINARY(32)) 

=> 0x427A20004257

SELECT CAST(N'穂　坂' COLLATE Chinese_Simplified_Pinyin_100_BIN2 AS VARBINARY(32))

=> 0x427A00304257

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.