SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Collation Issues

 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'

Query 2:
  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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.