Using Unicode characters in SQL Server

In troubleshooting an issue this week involving the storage of Japanese characters to the database, I found out some things.

When I ran a query on the database using MS SQL Server (2005) Management Studio, the row that should have looked like this

instead looked like question marks:

??????

Is it just my glasses?

My reaction in a situation like this is: “Is it really foggy out, or is it just my glasses?”  Either the data was being stored incorrectly in the database or I was simply having trouble viewing it.

Answers

Here is a dump of the things I learned:

• The question marks indicate that the data was really not stored correctly (data loss).
• If instead, your query results appear as square boxes (▯▯▯▯▯▯), your PC just does not have the language pack installed to view the characters.  On Windows XP, go to Control Panel -> Regional and Language Options and check the Install files for East Asian languages checkbox.
• One possible cause of your data turning to question marks is if you insert a literal unicode string but do not use the N prefix (e.g., N‘myunicodestring’).  Setting it as just ‘myunicodestring’ can cause non-Latin characters to go to question marks.
• Another cause is if your column is not of a unicode type.  For instance, on SQL Server, you would want to use one of the n- character column types such as nvarchar(x).