NVARCHAR2 are Unicode data types that store Unicode character data. If
the database Character set is UTF% e.g. AL16UTF16
then varchar2 also support Unicode data.
Check Database
Character set in database:
select * from
nls_database_parameters where
parameter like '%CHARACTERSET';
PARAMETER
VALUE
----------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_CHARACTERSET- This is for CHAR/VARCHAR2. Each character is takes 1 to 4 bytes to
store.
NLS_NCHAR_CHARACTERSET
– This is for NCHAR/NVARCHAR2.
Each character is either 2 or 4 bytes to store. So any character in NVARCHAR2
will not occupy less than 2 bytes.
So
if you have “Normal” character set in most of the rows then varchar2 is better
than nvarchar2 if your database character set is UTF%, since varchar2 will use 1
byte for every normal character and nvarchar2 will use 2 bytes for every normal
character.
Example:
create table test
(
y varchar2(50 char),
z nvarchar2(50)
);
Lets
inset something in English and other language(I have inserted something in Bengali
and Chinese)
insert into test values('কেমন আছ তুমি','কেমন আছ তুমি');
insert into test values('你好','你好');
insert into test values('How
are you','How
are you');
insert into test values('I am
fine','I am fine');
Check:
select y,vsize(y),z,vsize(z) from test;
Y
|
VSIZE(Y)
|
Z
|
VSIZE(Z)
|
কেমন আছ তুমি
|
32
|
কেমন আছ তুমি
|
24
|
你好
|
6
|
你好
|
4
|
How are you
|
11
|
How are you
|
22
|
I am fine
|
9
|
I am fine
|
18
|
Here
you can see for every English normal character set nvarchar2 occupied 2 bytes
where varhar2 occupied 1 bytes.