Why are and are the same in SQL Server

Question

In SSMS v17.9, connecting to an SQL Server 14.0.1000 instance, the system treats these two character the same: and

I set a [Kanji] table with the [Char] NVARCHAR(2) as the primary key. After I added "", I cannot add "" since it threw a "key duplication" error.

I ran this T-SQL:

IF ( N'' = N'') PRINT 'true' ELSE PRINT 'false'

The result prints out 'true'.

Answer 1

you have to use proper collation,

IF ( N'' COLLATE Chinese_Simplified_Stroke_Order_100_CI_AI = N'' COLLATE Chinese_Simplified_Stroke_Order_100_CI_AI) PRINT 'true' ELSE PRINT 'false'

collation can set on the fly in select statement or in DB object level

read more about collations

Answer 2

Based on a definition from Wikipedia: https://en.wikipedia.org/wiki/Collation

A collation algorithm such as the Unicode collation algorithm defines an order through the process of comparing two given character strings and deciding which should come before the other. When an order has been defined in this way, a sorting algorithm can be used to put a list of any number of items into that order.

When you refer to a character, that SQL Server does not have any scene about it, then SQL Server will treat it as ? character, therefor you will see that both and characters are same, because both are treated as ?

To prevent such a behaviour, you need to configure suitable collation on your database or table column or string literal.

So as @Hiran posted(https://stackoverflow.com/a/56220111/1666800) you will need to configure suitable collation.

Read more about collation in SQL Server: https://learn.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-2017

Here are some codes which will help you to configure the collation:

Set Collation on database:

ALTER DATABASE <Database>
COLLATE <Your Collation> ;
GO

Set Collation on Table Column:

USE <Database>
GO
ALTER TABLE <Table Nme>
ALTER COLUMN <Column Name> <Data Type>
COLLATE <Your Collation> <Other options>
GO

Set Collation on a string literal:

SELECT N'' COLLATE <Your Collation>

Answer 3

Besides the collations I'd suggest to have a look into the unicode code points or the binary representation:

SELECT UNICODE(N''), CAST(N'' AS BINARY(4))
     , UNICODE(N''), CAST(N'' AS BINARY(4))

The result

55384   0x58D84CDF  
55391   0x5FD8B7DF