nvarchar类型的不区分大小写的主键，其中ß！= ss [英] Case-insensitive primary key of type nvarchar where ß != ss

问题描述

此问题 需要使用不区分大小写的排序规则，其中ss ！=ß解决了 varchar 类型的列，但我的必须是 nvarchar 。

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

据我所知， SQL_Latin1_General_Cp437_BIN 区分ß和 ss 。但这也是区分大小写的。我的是一个主键列，也需要不区分大小写：

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:

我需要例如weiß / Weiß被视为相等，并且 weiss / Weiss ，但不是weiß / weiss 或Weiß / Weiss 或weiß / Weiss 等。

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.

推荐答案

我能找到的最接近的解决方法是使用索引视图在 UPPER（ID）上添加唯一约束，以停止使用的二进制排序规则未拾取的主键冲突在实际的主键上，例如

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');

但是以下操作将失败：

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

无法在对象'dbo.Collat​​ionTestConstraint'中插入具有唯一键的重复键行索引 UQ_Collat​​ionTestConstraint_ID。重复的键值为（WEIß）。

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

不能在具有唯一索引 UQ_Collat​​ionTestConstraint_ID的对象 dbo.Collat​​ionTestConstraint中插入重复的键行。重复的键值为（WEISS）。

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.

编辑

我肯定会感到复杂，您可以通过计算列和唯一约束来实现同一目的：

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)
);

这篇关于nvarchar类型的不区分大小写的主键，其中ß！= ss的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！