Case-insensitive primary key of type nvarchar where ß != ss

Question

This question "Need a case insensitive collation where ss != ß" solves it for varchar type column, but mine has to be nvarchar.

As far as I can gather, SQL_Latin1_General_Cp437_BIN differentiates between ß and ss. But it is also case-sensitive. Mine is a primary key column which also needs to be case INsensitive:

I need e.g. weiß/Weiß to be considered equal, and also weiss/Weiss, but NOT weiß/weiss nor Weiß/Weiss nor weiß/Weiss etc.

I've searched a lot for this, and is it really so that I'm out of luck here? There has to be a lot of people in the same situation, I just can't believe this could be unsolvable.

Answer 1

The closest I can get to a workaround is using an indexed view to add a unique constraint on UPPER(ID) to stop primary key violation that is not picked up by the binary collation used on the actual primary key, e.g.

CREATE TABLE CollationTest 
(
        ID NVARCHAR(50) COLLATE Latin1_General_BIN NOT NULL,
    CONSTRAINT PK_CollationTest_ID PRIMARY KEY (ID)
);
GO
CREATE VIEW dbo.CollationTestConstraint
WITH SCHEMABINDING
AS
    SELECT  ID = UPPER(ID)
    FROM    dbo.CollationTest;
GO
CREATE UNIQUE CLUSTERED INDEX UQ_CollationTestConstraint_ID 
    ON dbo.CollationTestConstraint (ID);

Now the following will work as the initial insert:

INSERT dbo.CollationTest (ID) VALUES ('weiß');
INSERT dbo.CollationTest (ID) VALUES ('Weiss');

But the following will fail:

INSERT dbo.CollationTest (ID) VALUES ('Weiß');
INSERT dbo.CollationTest (ID) VALUES ('weiss');

Cannot insert duplicate key row in object 'dbo.CollationTestConstraint' with unique index 'UQ_CollationTestConstraint_ID'. The duplicate key value is (WEIß).

Cannot insert duplicate key row in object 'dbo.CollationTestConstraint' with unique index 'UQ_CollationTestConstraint_ID'. The duplicate key value is (WEISS).

This appears to match your criteria after initial testing.

EDIT

I definitely over complicated this, you can achieve the same thing with a computed column and a unique constraint:

CREATE TABLE CollationTest 
(
        ID NVARCHAR(50) COLLATE Latin1_General_BIN NOT NULL,
        IDUpper AS UPPER(ID),
    CONSTRAINT PK_CollationTest_ID PRIMARY KEY (ID),
    CONSTRAINT UQ_CollationTest_IDUpper UNIQUE (IDUpper)
);

Answer 2

Ah, but it is possible as there is a collation that handles this naturally (several actually, but all in the same "family"): Hungarian_Technical_* (well, minus the CaseSensitive (CS) and Binary (BIN / BIN2) variations). Of course, I'm still not sure this is a good choice for a PK, but no reason it can't be a UNIQUE INDEX.

SELECT tmp.*,
       IIF(tmp.[First] = tmp.[Second] COLLATE Hungarian_Technical_CI_AI,
           N'Equal',
           N'NOT EQUAL') AS [Result]
FROM (
      SELECT N'weiß', N'Weiß', N'Equal'
      UNION ALL
      SELECT N'weiss', N'Weiss', N'Equal'
      UNION ALL
      SELECT N'weiß', N'weiss', N'NOT Equal'
      UNION ALL
      SELECT N'Weiß', N'Weiss', N'NOT Equal'
      UNION ALL
      SELECT N'weiß', N'Weiss', N'NOT Equal'
      ) tmp ([First], [Second], [Ideal]);

Results:

First   Second  Ideal       Result
weiß    Weiß    Equal       Equal
weiss   Weiss   Equal       Equal
weiß    weiss   NOT Equal   NOT EQUAL
Weiß    Weiss   NOT Equal   NOT EQUAL
weiß    Weiss   NOT Equal   NOT EQUAL

There are 24 total collations that will work in this manner. You can find them via:

SELECT hc.*
FROM sys.fn_HelpCollations() hc
WHERE hc.name LIKE N'Hungarian[_]Technical%'
AND hc.name LIKE N'%[_]CI%'
ORDER BY hc.name;

They are:

Hungarian_Technical_100_CI_AI Hungarian_Technical_100_CI_AI_KS
Hungarian_Technical_100_CI_AI_KS_SC
Hungarian_Technical_100_CI_AI_KS_WS
Hungarian_Technical_100_CI_AI_KS_WS_SC
Hungarian_Technical_100_CI_AI_SC Hungarian_Technical_100_CI_AI_WS
Hungarian_Technical_100_CI_AI_WS_SC Hungarian_Technical_100_CI_AS
Hungarian_Technical_100_CI_AS_KS Hungarian_Technical_100_CI_AS_KS_SC
Hungarian_Technical_100_CI_AS_KS_WS
Hungarian_Technical_100_CI_AS_KS_WS_SC
Hungarian_Technical_100_CI_AS_SC Hungarian_Technical_100_CI_AS_WS
Hungarian_Technical_100_CI_AS_WS_SC Hungarian_Technical_CI_AI
Hungarian_Technical_CI_AI_KS Hungarian_Technical_CI_AI_KS_WS
Hungarian_Technical_CI_AI_WS Hungarian_Technical_CI_AS
Hungarian_Technical_CI_AS_KS Hungarian_Technical_CI_AS_KS_WS
Hungarian_Technical_CI_AS_WS

If the version 100 collations are available (meaning: you are using SQL Server 2008 or newer), then use those and not the collations with no version number in their names.

EDIT:
I found some additional info on the "Hungarian Technical" collation that might be of interest:

• MySQL: Hungarian collation -- This is an archive of a listserv discussion by some MySQL developers trying to figure out the specifics of how to implement the collation. Apparently it has some complex rules, due at least in part to needing to equate multiple characters into a single character for sorting (look for section "3. Special digraph/trigraph rule"). This might cause some unexpected behavior. The specific letter combinations are noted in that section so at least it makes it easier to set up a test case to help determine if the sorting rules invalidate using this collation.

• Why are there so many(106) Hungarian collations? -- This is a discussion on social.msdn that has some interesting info. There is a comment in there by Erland Sommarsko in which he states:

  I ran a crazy performance test to test all collations in SQL 2008. I had an idle server available, and the test ran for 8-9 days. And Hungarian_Technical came out as the slowest as I recall.

  Given the discussion from the MySQL devs about the complex rules, those test results don't seem all that surprising.

So, both of these items probably should be factored into deciding whether or not to go the easy route of simply setting the NVARCHAR field to one of these Hungarian Technical collations, or going with @GarethD's recommendation of the computed column.

For more info on working with strings and collations, please visit: Collations Info