Sql Server allows storing unicode data using appropriate datatypes i.e. NVARCHAR, etc. But we can also store unicode data in non-unicode data types by specifying COLLATE keyword too. Important point here is, no matter what method you use, you must specify ‘N’ before the unicode literal in your INSERT statements.

Example:

[sourcecode language=”sql”]
CREATE TABLE dbo.Unicode_Test
(
Language_Name VARCHAR(80) NOT NULL
, Native_Name NVARCHAR(100) NOT NULL
)
ON [PRIMARY]
GO

INSERT INTO dbo.Unicode_Test (Language_Name ,Native_Name)
VALUES (
‘Abkhaz’ — Language Name
, N’аҧсуа бызшәа, аҧсшәа’ — Native Language
),
(
‘Arabic’ — Language Name
, N’العربية’ — Native Language
),
(
‘Armenian’ — Language Name
, N’Հայերեն’ — Native Language
),
(
‘Assamese’ — Language Name
, N’অসমীয়া’ — Native Language
);
GO
[/sourcecode]

Important part here is to have ‘N’ before the native language literal (unicode column value).

Here is the result:

Results of Unicode Table

When you INSERT the same data into the same table without ‘N’ the result will bevery different.

[sourcecode language=”sql”]
INSERT INTO dbo.Unicode_Test (Language_Name ,Native_Name)
VALUES (
‘Abkhaz’ — Language Name
, ‘аҧсуа бызшәа, аҧсшәа’ — Native Language
),
(
‘Arabic’ — Language Name
, ‘العربية’ — Native Language
),
(
‘Armenian’ — Language Name
, ‘Հայերեն’ — Native Language
),
(
‘Assamese’ — Language Name
, ‘অসমীয়া’ — Native Language
);
GO
[/sourcecode]

Results without N

N tells Sql Server that the literal that is encapsulated in the quotes is a unicode data type and must be treated that way.

Hope this helps,

_SqlTimes