[valiant55]

There's a data type precedence that it uses to determine which value should be casted[0]. Nvarchar is higher precedence, therefore the varchar value is "lifted" to an nvarchar value first. This wouldn't be an issue if the types were reversed.

0: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-...

[Sankozi]

So such issues can appear in more products and more datatypes (int and bigint have same problem).

This is really bad rule for SQL's "equality" operator.

Still optimizer should be able to handle it - if the result is the same, optimizer should take faster path.