Even Monkeys fall from Trees
There's an adage in Japanese that “even Monkeys fall from trees,“ roughly meaning that even experts sometimes make mistakes. From a design point-of-view, I usually try to use NVARCHAR instead VARCHAR by default mostly because assuming everything will representable in English forever seems terribly short-sighted to me. Having to go back and retrofit a database to accomodate a simple business need like being able to enter non-English data seems like saving pennies and spending dollars.
That said, I forgot how to do it. When you're inserting two-byte character data into a table, be sure preface it with an upper case 'N.' To wit:
create table dbo.sayings(id tinyint identity(1,1), saying_en varchar(255), saying_jp nvarchar(512))
go
insert into sayings(saying_en,saying_jp) values ('Even mokeys fall from trees','猿も木から落ちる')
insert into sayings(saying_en,saying_jp) values ('Even mokeys fall from trees',N'猿も木から落ちる')
select * from sayings saying for xml auto,root('sayings')
go
Yields:
<sayings>
<saying id="1" saying_en="Even mokeys fall from trees" saying_jp="????????" />
<saying id="2" saying_en="Even mokeys fall from trees" saying_jp="猿も木から落ちる" />
</sayings>
'nuff said?