We have an nvarchar and have noticed that some extended/special characters are being identified as the same as other characters, for example the same as SS

When using varchar and collation Latin1_General_CI_AS they are identified as being the same

SELECT distinct val 
from (values (''),('SS') ) x(val) 

RESULT
------

When using collation SQL_Latin1_General_CP1_CI_AS, they are determined as distinct

SELECT distinct val COLLATE SQL_Latin1_General_CP1_CI_AS
from (values (''),('SS') ) x(val) 

RESULT
------
SS

But when using nvarchar, they are again identified as the same

SELECT distinct cast(val as nvarchar) 
from (values (''),('SS') ) x(val) 

RESULT
------

Can anyone suggest a setting to allow us to identify these as distinct? These values are coming in from SSIS where they are identified as distinct but they're hitting a unique index constraint once loaded to SQL Server.

You'll find your answer here: http://stackoverflow.com/questions/29845872/why-is-ss-equal-to-the-german-sharp-s-character-%C3%9F

Funny. When I run the 3rd query I get "SS" as the result. I'm not sure what your collation is.

Here's one thing to consider: 

• ALWAYS declare your NVARCHAR values with N like this: 

SELECT distinct val COLLATE SQL_Latin1_General_CP1_CI_AS
from (values (N''),(N'SS') ) x(val) 

--RESULTS: 

SELECT distinct val
from (values (N''),(N'SS') ) x(val) 

--RESULTS: 

SELECT distinct cast(val as nvarchar) 
from (values (N''),(N'SS') ) x(val) 

--RESULTS: 


--Your queries:
SELECT distinct val COLLATE SQL_Latin1_General_CP1_CI_AS
from (values (''),('SS') ) x(val) 

--RESULTS: , SS

SELECT distinct val
from (values (''),('SS') ) x(val) 

--RESULTS: , SS

SELECT distinct cast(val as nvarchar) 
from (values (''),('SS') ) x(val) 

--RESULTS: SS

What even better is when you run:

SELECT distinct val
from (values (N''),(N'SS'), (N'S') ) x(val) 

--RESULT = SS, S

And wait for it, wait for it... BAM!:

Bing search for shows starts showing SS :)

Sadly google does not. :(

I hope you found this post helpful! If you did please vote it as helpful on the left. If it answered your question please mark it as the answer below. :)

• Edited by Daniel Janik 6 hours 49 minutes ago