SQL: When inserting SQL Server data in other languages doesn't work as expected

This post relates to another question I got on Stack Overflow recently.

The poster was saying that he was having no luck inserting data from teh Gujarati language, even though he was using nvarchar as a data type.

The most common problem that I see when people aren't getting the outcome they want when inserting into an nvarchar column is that they aren't putting N in front of their string values. (N is National Characters Set). Imagine a table like this:

Here's a Chinese example that won't work (because of multi-byte string values):

You won't get an error on the insert, but if you then query it, you'll see this:

With just single quotes, it's just an ANSI string, basically made up of ASCII values, and so your multibyte characters get messed up.

Let's try that with and without the N using Gujarati:

You can see the output in the main image above. Note that in rows 1 and 2, the characters are almost what was input. In this case, there is a basic mapping to single byte characters that worked out not too bad, but often you'll just see ?? instead.

In rows 3 and 4, where we used the N for the string definition, all worked as expected.