http://www.sqlservercentral.com/blogs/steve_jones/2008/05/19/collation-issues/ Printed 2016/06/30 08:32AM
This is really interesting to me:
| CREATE TABLE #TEMP (name varchar(80))|
INSERT INTO #TEMP values ('Malteser Schloßschule')
INSERT INTO #TEMP values ('Malteser Schlossschule')
CREATE TABLE #TEMP1 (name nvarchar(160))
INSERT INTO #TEMP1 values ('Malteser Schloßschule')
INSERT INTO #TEMP1 values ('Malteser Schlossschule')
SELECT * FROM #TEMP WHERE name ='Malteser Schlossschule'
-- Results :- 'Malteser Schlossschule'
SELECT * FROM #TEMP1 WHERE name ='Malteser Schlossschule'
-- Results :- Malteser Schloßschule
-- Malteser Schlossschule
I posted a response to the person that it must be a conversion with Unicode that allows things to match up, but they don't in non-Unicode. I'd expect that this would work the other way, since I'd think that the first "s" would get converted in the varchar to an "s" and it would work correctly.
Someone else said to be sure you're using Accent Sensitive collation, which would remove the row from the second one, but I'm still not sure why this works. I should send to Sorting It All Out blog.