Why does SQL Server ignore emoji in an equality expression?

Question

Today I ran into an interesting problem and wanted to know the reasons for this behavior. I have a user table with usernames, and I request it to pull out a unique user, and then do a password check with all the hashing and salty kindness.

However, if I put emojis in the request, the user pulled it out of the database anyway, and I would like to know why and what settings should be applied. I use EF, but I tested raw T-SQL, and the behavior is the same as EF is not the culprit.

SELECT TOP 1 * 
FROM Users 
WHERE username = N'someuser' --Works as expected

SELECT TOP 1 * 
FROM Users 
WHERE username = N'some🐶user🐶' --ALSO WORKS!

I can put emojis anywhere and anytime, and the user is still returning. I can obviously install C # code that will perform additional checks, so this problem is solvable there, but I would like it to be solved at the database level, as there can be many other queries that perform string comparisons.

Emojis in the password is not a problem, since hashing and salting will be applied in C #, so emojis in the password is fine.

Answer 1

, , emoji, . , , , :

select *
from (values
    (N'some🐶user🐶', N'someuser')
  , (N'someuser', N'someuser')
  , (N'some🐶user🐶', N'some🐶user🐶')
) as a (L, R)
where a.L = a.R collate Latin1_General_100_CI_AS_SC;

. MSDN .