'ß' and 'ss' are NOT equal by BetterToday

Below is a way you might work around this. A second column with BIN collation is added to the primary key that contains only the sequence of appearances of 'ss' and 'ß' in the first column.

Notes: The function below turns triple-s into 'ss', but the workaround should correctly distinguish ss and ß where it needs to. You would want to analyze and test this carefully if you decide to use it. Also, depending on how data enters your table, you would either change the input routines or create a view without the extra column and use INSTEAD OF INSERT/UPDATE triggers to maintain the view. I didn't try a persisted computed column, and I don't know if all the determinism rules are satisfied.

Another possibility is to use a collation (such as Albanian or Latin1_General_BIN) that distinguishes ess from eszet, but I assume that's not an option, because it changes other collation rules also (the legacy SQL collations might come close, though).

Unfortunately, the eszet is a murky corner of written language - Unicode has or will have an UPPER-case sharp-s also, just to create more confusion.

create function ss_eszet(
@s nvarchar(max)
) returns nvarchar(max) as begin
if @s is null return null;
declare @result nvarchar(max);
set @result = N'';
declare @i int;
set @i = 1;
declare @p int;
while @i < len(@s) begin
    set @p = charindex('ss' collate latin1_general_ci_as,@s collate latin1_general_ci_as,@i);
    if @p = 0 break;
    set @result = @result + substring(@s,@p,1);
    set @i = @p + 1;
end
return @result
end;
go

create table T(
s varchar(100) collate latin1_general_ci_as not null,
ss_eszet varchar(100) collate latin1_general_bin not null,
primary key(s,ss_eszet)
)
go

insert into T values ('Maße',dbo.ss_eszet(N'Maße'));
insert into T values ('Masse',dbo.ss_eszet(N'Masse'));
go
insert into T values ('Maße',dbo.ss_eszet(N'Maße'));
go

insert into T values ('Masse',dbo.ss_eszet(N'Masse'));
go

select s from T;
go

drop function ss_eszet;
drop table T;