Solomon Rutzky digs into collations:
So, the actual question is a bit more specific than would reasonably fit into a title, and it is:
WHEREcondition (in Microsoft SQL Server, if that’s not obvious), when a string constant containing Unicode characters but not prefixed with a capital-
Nis compared to a column, which Collation (and hence Code Page) is used to do the implicit conversion of the Unicode characters into
VARCHAR? Is it the Database’s default Collation, or the column’s Collation?
The answer has a lot more nuance than you might first think. And for the record, it’s a duck.