SQL Server Collation, Management Studio Results differ from asp.net Results

Question

i am usering an Table with a user name column and collation SQL_Latin1_General_CP1_CI_AS

now the query:

select 1 where exists(select 1 FROM USER_TABLE WITH(NOLOCK) WHERE user_name='sueßemaus')

in asp.net results: 1 in SQL Management Studio results: [nothing]

there is a record with name "süßemaus" compared to collation the correct Result is [nothing] but why does asp.net ignores the collation?

Answer 1

try select 1 where exists(select 1 FROM USER_TABLE WITH(NOLOCK) WHERE user_name='sueßemaus' collate SQL_Latin1_General_CP1_CI_AS)

Answer 2

You don't need the outer query. This will do.

select 1 FROM USER_TABLE WITH(NOLOCK) WHERE user_name='sueßemaus'

Now, you mentioned "süßemaus" but the query is "sueßemaus". It may be the same when you don't have umlauts on your keyboard and you're reading it as a human being, but this is a completely different query because ü <> ue

I deal with many names with ä ü ö etc and collation "SQL_Latin1_General_CP1_CI_AS"

Now, it may work if you coerce collation to a German one, but I've never tried, and I don't know if it will work out the interchange of ü -> ue or ue -> ü. I suspect it won't because with case insensivity and auto translation Uetliberg would become a very wrong ülitberg. (800+ meter hill just west of Zurich)