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

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)

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.

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.

• 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!