When to use varchar2 instead of nvarchar2

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.

Comments