SQL Server 2014 inconsistent behavior when handling unicode

Viewed 106

My goal is to enforce case-insensitive uniqueness on a column that may contain unicode characters on any unicode planes. But I encountered some inconsistent behavior when testing against SQL Server 2014(build 12.0.4213.0). Here is the test:

CREATE TABLE unicodetest1 (
  id int, 
/* this collation is supposed to support all supplementary unicode characters */
  a nvarchar(10) COLLATE Latin1_General_100_CI_AS_SC
);

CREATE UNIQUE INDEX idx_a_1 ON unicodetest1(a);

I can insert many single unicode characters, both on BMPs and SMP (Plane 1):

INSERT INTO unicodetest1(id, a) VALUES(1, 't'); -- U+0074
INSERT INTO unicodetest1(id, a) VALUES (100, N'');  -- U+1F478
INSERT INTO unicodetest1(id, a) VALUES (101, N'');  -- U+1F479
INSERT INTO unicodetest1(id, a) VALUES (104, N'⭐');  -- U+2b50

SELECT id, a, len(a) FROM unicodetest1;

The length of the single character strings is 1, which is expected.

When I concatenate some unicode characters to ascii strings, it still works:

INSERT INTO unicodetest1(id, a) VALUES (111, N'test');  -- ok
INSERT INTO unicodetest1(id, a) VALUES (112, N'test');  -- ok
INSERT INTO unicodetest1(id, a) VALUES (113, N'test');  -- ok

So far so good.

But, when I insert N'test⭐', I get a duplicate key violation:

INSERT INTO unicodetest1(id, a) VALUES (114, N'test⭐');  -- dup

Msg 2601, Level 14, State 1, Line 21
Cannot insert duplicate key row in object 'dbo.unicodetest1' with unique index 'idx_a_1'. The duplicate key value is (test⭐).

The character "⭐" has a code point U+2b50, it is on BMP!

It seems that SQL Server thinks N'test⭐' and N'test' are identical!

My question is: what makes "⭐" special that it violates uniqueness constraint when it is concatenated to a string?

Thanks

James

0 Answers
Related