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'.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| USE tempdb; | |
| GO | |
| DROP TABLE IF EXISTS dbo.NonUnicode; | |
| DROP TABLE IF EXISTS dbo.Unicode; | |
| CREATE TABLE dbo.NonUnicode (NU varchar(1) NOT NULL DEFAULT 'a') | |
| CREATE TABLE dbo.Unicode (U nvarchar(1) NOT NULL DEFAULT N'a'); | |
| INSERT INTO dbo.NonUnicode (NU) DEFAULT VALUES; | |
| INSERT INTO dbo.Unicode (U) DEFAULT VALUES; | |
| SELECT NU FROM dbo.NonUnicode; | |
| SELECT U FROM dbo.Unicode; | |
| — Turn on 'Actual Execution Plan'. | |
| SELECT NU, U | |
| FROM dbo.NonUnicode | |
| JOIN dbo.Unicode | |
| ON dbo.NonUnicode.NU = dbo.Unicode.U; |
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…
… 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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH Algorithms ([algorithm]) AS ( | |
| SELECT [algorithm] | |
| FROM (VALUES ('MD2'), ('MD4'), ('MD5'), ('SHA'), ('SHA1'), ('SHA2_256'), ('SHA2_512')) AS t([algorithm]) | |
| ) | |
| SELECT [algorithm], | |
| t.hashed_varchar, | |
| u.hashed_nvarchar, | |
| checked_varchar = CHECKSUM('a'), | |
| checked_nvarchar = CHECKSUM(N'a') | |
| FROM Algorithms | |
| CROSS APPLY (SELECT HASHBYTES([algorithm], CAST('a' AS varchar(1)))) AS t(hashed_varchar) | |
| CROSS APPLY (SELECT HASHBYTES([algorithm], CAST('a' AS nvarchar(1)))) AS u(hashed_nvarchar); | |
| GO |
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…