I currently have a table that has 4 columns. The ID of the object, ID of another object in another table, nvarchar data, and a bool.
PK is made up of the first 3 columns.
The values größe conflicts with grösse, and große conflicts with grosse
meaning I can have one of the first two and one of the second two, but not all of them
The column has collation set to SQL_Latin1_General_CS_AS
I believe this is where the problem lies but this does handle many other unicode characters correctly. Has anyone encountered this and know what my problem is?
For reference both of these play okay with all of the above.
gråsse
grøsse
Example for clarity, for me this is printing equal:
IF (N'grösse' COLLATE Latin1_General_CS_AS = N'größe' COLLATE Latin1_General_CS_AS)
BEGIN
PRINT 'EQUAL'
END
When I expect these to be different.
handle many other unicode characters correctly
What does correctly mean to you? The different collations in SQL Server have different behavior. Maybe you are looking for a binary collation like LATIN1_GENERAL_BIN2. This one compares code-points only. Duplicates will only occur when the strings are binary-identical. Your example code would behave like you want it to.
The non-binary collations try to apply lexicographic rules. They sort and compare strings like a phone book would.