Difference between VARCHAR2 10 CHAR and NVARCHAR2 10

Question

I ve installed Oracle Database 10g Express Edition  Universal  with the default settings  SELECT   FROM NLS DATABASE PARAMETERS   NLS CHARACTERSET               AL32UTF8                                  NLS NCHAR CHARACTERSET         AL16UTF16                                  Given that both CHAR and NCHAR data types seem to accept multi-byte strings  what is the exact difference between these two column definitions  VARCHAR2 10 CHAR  NVARCHAR2 10

User · Answer

nVarchar2 is a Unicode-only storage   Though both data types are variable length String datatypes  you can notice the difference in how they store values   Each character is stored in bytes  As we know  not all languages have alphabets with same length  eg  English alphabet needs 1 byte per character  however  languages like Japanese or Chinese need more than 1 byte for storing a character   When you specify varchar2 10   you are telling the DB that only 10 bytes of data will be stored  But  when you say nVarchar2 10   it means 10 characters will be stored  In this case  you don t have to worry about the number of bytes each character takes

User · Answer

The NVARCHAR2 datatype was introduced by Oracle for databases that want to use Unicode for some columns while keeping another character set for the rest of the database  which uses VARCHAR2   The NVARCHAR2 is a Unicode-only datatype   One reason you may want to use NVARCHAR2 might be that your DB uses a non-Unicode character set and you still want to be able to store Unicode data for some columns without changing the primary character set  Another reason might be that you want to use two Unicode character set  AL32UTF8 for data that comes mostly from western Europe  AL16UTF16 for data that comes mostly from Asia for example  because different character sets won t store the same data equally efficiently   Both columns in your example  Unicode VARCHAR2 10 CHAR  and NVARCHAR2 10   would be able to store the same data  however the byte storage will be different  Some strings may be stored more efficiently in one or the other   Note also that some features won t work with NVARCHAR2  see this SO question    Oracle Text will not work with NVARCHAR2  What else might be unavailable

User · Answer

The NVARCHAR2 stores variable-length character data  When you create a table with the NVARCHAR2 column  the maximum size is always in character length semantics  which is also the default and only length semantics for the NVARCHAR2 data type   The NVARCHAR2data type uses AL16UTF16character set which encodes Unicode data in the UTF-16 encoding  The AL16UTF16 use 2 bytes to store a character  In addition  the maximum byte length of an NVARCHAR2 depends on the configured national character set  VARCHAR2 The maximum size of VARCHAR2 can be in either bytes or characters  Its column only can store characters in the default character set while the NVARCHAR2 can store virtually any characters  A single character may require up to 4 bytes    By defining the field as    VARCHAR2 10 CHAR  you tell Oracle it can use enough space to store 10 characters  no matter how many bytes it takes to store each one  A single character may require up to 4 bytes  NVARCHAR2 10   you tell Oracle it can store 10 characters with 2 bytes per character   In Summary     VARCHAR2 10 CHAR   can store maximum of 10 characters and maximum of 40 bytes  depends on the configured national character set   NVARCHAR2 10   can store maximum of 10 characters and maximum of 20 bytes  depends on the configured national character set     Note  Character set can be UTF-8  UTF-16       Please have a look at this tutorial for more detail   Have a good day

User · Answer

I don t think answer from Vincent Malgrat is correct  When NVARCHAR2 was introduced long time ago nobody was even talking about Unicode   Initially Oracle provided VARCHAR2 and NVARCHAR2 to support localization  Common data  include PL SQL  was hold in VARCHAR2  most likely US7ASCII these days  Then you could apply NLS NCHAR CHARACTERSET individually  e g  WE8ISO8859P1  for each of your customer in any country without touching the common part of your application   Nowadays character set AL32UTF8 is the default which fully supports Unicode  In my opinion today there is no reason anymore to use NLS NCHAR CHARACTERSET  i e  NVARCHAR2  NCHAR2  NCLOB  Note  there are more and more Oracle native functions which do not support NVARCHAR2  so you should really avoid it  Maybe the only reason is when you have to support mainly Asian characters where AL16UTF16 consumes less storage compared to AL32UTF8

[oracle] Difference between VARCHAR2(10 CHAR) and NVARCHAR2(10)

Examples related to oracle

Examples related to oracle-xe