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