What Happens When Unicode Strings Concatenate with Non-Unicode Strings?
If a Unicode string NVARCHAR is concatenated with a non-Unicode string VARCHAR, SQL Server will implicitly convert the non-Unicode string to Unicode string for concatenation.
