Time to read: around 2 minutes.
I would like to think that there is enough information out there so that we know about the differences between using
Yet, apart from when they cause Implicit Conversions in Execution Plans, I’ve never really noticed the difference between comparing
The above code shows, when you include the Actual Execution plan, that the non-Unicode column gets implicitly converted up to Unicode character.
I say “up” because it follows the data type precendence as stated in docs.microsoft.
But they match…
…and you can see that they match because we get a result back. The join is able to go ahead even with the conversion as SQL Server basically treats them as … well … a = a!
In my head it didn’t matter what you did with the two values, they would always evaluate to each other.
SELECT ASCII('a') AS ascii_non_unicode, ASCII(N'a') AS ascii_unicode;
SELECT UNICODE('a') AS unicode_non_unicode, UNICODE(N'a') AS unicode_unicode;
So imagine my surprise, confusion, annoyance, and then finally acceptance when I found an example where they don’t match.
Where they don’t match…
Not the most dramatic of reveals, I know 😦
Don’t believe me? Here’s how to reproduce it.
This lead to the first time…
… that I can recall where I had the following conversation with another person.
Them: I’m getting a different result.
Me: Did you put in N’a’.
Them: Yeah I put in ‘a’.
Me: You can’t have. Look!
Them: But I did! Look!
Me: … what the… ?
Oh well, the more you know…