问题描述
此问题 解决了 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');
不能在具有唯一索引 UQ_CollationTestConstraint_ID的对象 dbo.CollationTestConstraint中插入重复的键行。重复的键值为（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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！