The difference between VARCHAR2 (10 CHAR) and NVARCHAR2 (10)

Question

I installed Oracle Database 10g Express Edition (Universal) with default settings:

SELECT * FROM NLS_DATABASE_PARAMETERS; NLS_CHARACTERSET AL32UTF8 NLS_NCHAR_CHARACTERSET AL16UTF16 

Given that the CHAR and NCHAR data types seem to accept multibyte strings, what is the exact difference between the two column definitions?

 VARCHAR2(10 CHAR) NVARCHAR2(10) 

Answer 1

NVARCHAR2 datatype was introduced by Oracle for databases that want to use Unicode for some columns, while maintaining a different character set for the rest of the database (which uses VARCHAR2). NVARCHAR2 is a Unicode-only data type.

One of the reasons you might want to use NVARCHAR2 may be because your database uses a character set other than Unicode, and you still want to be able to store Unicode data for some columns without changing the main character set. Another reason may be that you want to use two Unicode character sets (AL32UTF8 for data that comes mostly from Western Europe, AL16UTF16 for data that comes mostly from Asia, for example), since different character sets won't keep the same data is equally effective.

Both columns in your example (Unicode VARCHAR2(10 CHAR) and NVARCHAR2(10) ) will be able to store the same data, however the byte storage will be different. Some rows can be stored more efficiently in one or the other.

Please note that some functions will not work with NVARCHAR2, see this SO question:

• Oracle Text will not work with NVARCHAR2. What else might not be available?

Answer 2

• NVARCHAR2 stores variable-length character data. When creating a table with NVARCHAR2 maximum size is always in the semantics of the character length, which is also the semantics of the default length and the only length for NVARCHAR2 data.

  NVARCHAR2 data NVARCHAR2 uses AL16UTF16 characters AL16UTF16 which encodes Unicode data in UTF-16 . AL16UTF16 uses 2 bytes to store a character. In addition, the maximum byte length of NVARCHAR2 depends on the configured national character set.

• VARCHAR2 maximum size of VARCHAR2 can be in bytes or characters. Its column can only store characters in the default character set, while NVARCHAR2 can store almost any character. Up to 4 bytes may be required per character.

Defining the field as:

• VARCHAR2(10 CHAR) you tell Oracle that it can use enough space to store 10 characters, regardless of how many bytes are required to store each of them. Up to 4 bytes may be required per character.
• NVARCHAR2(10) you tell Oracle that it can store 10 characters at 2 bytes per character

Eventually:

• VARCHAR2(10 CHAR) can store a maximum of 10 characters and a maximum of 40 bytes (depending on the configured set of national characters).

• NVARCHAR2(10) can store a maximum of 10 characters and a maximum of 20 bytes (depending on the configured national character set).

Note: the character set can be UTF-8 , UTF-16 , ....

Please see this guide for more details.

Have a nice day!

Answer 3

I do not think the answer from Vincent Mulgrat is correct. When NVARCHAR2 was introduced a long time ago, no one even talked about Unicode.

Oracle originally provided VARCHAR2 and NVARCHAR2 to support localization. Shared data (including PL / SQL) was stored in VARCHAR2 , most likely US7ASCII these days. You can then apply NLS_NCHAR_CHARACTERSET individually (for example, WE8ISO8859P1 ) for each of your customers in any country without affecting the general part of your application.

Currently, the AL32UTF8 character set is fully Unicode- AL32UTF8 by default. In my opinion, today there is no more reason to use NLS_NCHAR_CHARACTERSET , that is, NVARCHAR2 , NCHAR2 , NCLOB . Note that more and more native Oracle features do not support NVARCHAR2, so you should avoid this. Perhaps the only reason is that you should support mostly Asian characters, where AL16UTF16 consumes less memory compared to AL32UTF8 .

Answer 4

nVarchar2 is a Unicode repository.

Although both data types are variable-length string types, you may notice a difference in the way they store values. Each character is stored in bytes. As you know, not all languages ​​have alphabets of the same length, for example, the English alphabet needs 1 byte per character, but characters, for example, Japanese or Chinese, require more than 1 byte to store characters.

When you specify varchar2 (10) , you tell the DB that only 10 bytes of data will be saved. But, when you say nVarchar2 (10) , it means that 10 characters will be saved. In this case, you do not need to worry about the number of bytes that each character accepts.

Answer 5

I wonder if NVARCHAR2 (1) and VARCHAR2 (1) act differently with respect to null / empty?

From testing, it seems like.

You can get some surprises.

i.e. comparing an empty string in! = 'Y' will not return empty string lines. those. an empty string does not have and is not equal to "Y" ...

need nvl wrapper function
e.g. nvl (top (WP. "OW_IS_MISRUN"), 'N')! = 'Y'

select count - (*) from "DATA_HUB". "OW_WELL_PERFORATION" WP where WP.UWI = 17038046

7

select count (*) from "DATA_HUB". "OW_WELL_PERFORATION" WP where WP.UWI = 17038046 and top (WP. "OW_IS_MISRUN")! = 'Y'

one

select count (*) from "DATA_HUB". "OW_WELL_PERFORATION" WP where WP.UWI = 17038046 and top (WP. "OW_IS_MISRUN") = 'Y'

2

select count (*) from "DATA_HUB". "OW_WELL_PERFORATION" WP where WP.UWI = 17038046 and nvl (upper (WP. "OW_IS_MISRUN"), 'N')! = 'Y'

5