Another Difference between N” and ” in SQL Server.

If it walks like a ‘duck’, talks like a ‘duck’, and looks like a ‘duck’…careful! It could be a N’duck’

Words: 270

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 nvarchar and varchar.

Yet, apart from when they cause Implicit Conversions in Execution Plans, I’ve never really noticed the difference between comparing 'a' to N'a'.

The above code shows, when you include the Actual Execution plan, that the non-Unicode column gets implicitly converted up to Unicode character.

ConvertImplicit.PNG
Type conversion in expression…

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;

AsciiUnicode.PNG

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…

… are from the two functions: HASHBYTES and CHECKSUM.

Not the most dramatic of reveals, I know 😦

Don’t believe me? Here’s how to reproduce it.

Reproduced.png
‘Apples’ <> N’Apples’.

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…

Author: Shane O'Neill

DBA, T-SQL and PowerShell admirer, Food, Coffee, Whiskey (not necessarily in that order)...

What's your opinion?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s