Unicode- VARCHAR and NVARCHAR

Question

-- Creating Table 

  Create Table Test1
  (
    id Varchar(8000)
  )

-- Inserting a record  
Insert into Test1 Values ('我們的鋁製車架採用最新的合金材料所製成，不但外型輕巧、而且品質優良。為了達到強化效果，骨架另外經過焊接和高溫處理。創新的設計絕對能充分提升踏乘舒適感和單車性能。');

As I have defined data type of id as Varchar. The data is stored as ?????.

Do I have to use NVARCHAR..? What is Difference between VarChar and Nvarchar(). Please explain about UNIcode as well.

Answer 1

Varchar uses Windows-1252 character encoding, which is for all practical purposes standard ASCII.

As others have noted, nvarchar allows the storage of unicode characters.

You can get the ASCII translations from either data type, as shown here:

IF OBJECT_ID('TEST1') IS NOT NULL
  DROP TABLE TEST1
GO
CREATE TABLE TEST1(VARCHARTEST VARCHAR(8000), NVARCHARTEST NVARCHAR(4000))

-- Inserting a record  
INSERT INTO TEST1 VALUES ('ABC','DEF')
SELECT
  VARCHARTEST
 ,NVARCHARTEST
 ,ASCII(SUBSTRING(VARCHARTEST,1,1))
 ,ASCII(SUBSTRING(VARCHARTEST,2,1))
 ,ASCII(SUBSTRING(VARCHARTEST,3,1))
 ,ASCII(SUBSTRING(NVARCHARTEST,1,1))
 ,ASCII(SUBSTRING(NVARCHARTEST,2,1))
 ,ASCII(SUBSTRING(NVARCHARTEST,3,1))
FROM
  TEST1
DROP TABLE TEST1

Answer 2

Nvarchar supports UNICODE. SO yes. you need to have the column as nvarchar and not varchar.

Answer 3

Despite the collation of your database. Use nvarchar to store UNICODE. Embbed your Unicode value in N'[value]'

INSERT INTO ... VALUES
('Azerbaijani (Cyrillic)', N'Aзәрбајҹан (кирил әлифбасы)', 'az-cyrl')

In DB: 59   Azerbaijani (Cyrillic)  Aзәрбајҹан (кирил әлифбасы) az-cyrl

Important is the N prefix!

Valid for MS SQL 2014 I am using. Hope this helps.

Answer 4

The column type nvarchar allows you to store Unicode characters, which basically means almost any character from almost any language (including modern languages and some obsolete languages), and a good number of symbols too.

Answer 5

also it is required to prefix N before your value. example Insert into Test1 Values (N'我們的鋁製車架採用最新的合金材料所製成，不但外型輕巧、而且品質優良。為了達到強化效果，骨架另外經過焊接和高溫處理。創新的設計絕對能充分提升踏乘舒適感和單車性能。'); or programatically use preparedstatement with bind values for inserting and updating natural characterset

Answer 6

Yes you have to use nvarchar or use a collation for the language set you want. But nvarchar is preferred. Goodgle can tell you what this stuff means.