Difference between VARCHAR2(11 BYTE) and VARCHAR2(11 CHAR)

Question

In Oracle, what is the difference between :

CREATE TABLE CLIENT
(
 NAME VARCHAR2(11 BYTE),
 ID_CLIENT NUMBER
)

and

CREATE TABLE CLIENT
(
 NAME VARCHAR2(11 CHAR), -- or even VARCHAR2(11)
 ID_CLIENT NUMBER
)

Thank you.

Answer 1

One has exactly space for 11 bytes, the other for exactly 11 characters. Some charsets such as Unicode variants may use more than one byte per char, therefore the 11 byte field might have space for less than 11 chars depending on the encoding.

See also http://www.joelonsoftware.com/articles/Unicode.html

Answer 2

I am not sure since I am not an Oracle user, but I assume that the difference lies when you use multi-byte character sets such as Unicode (UTF-16/32). In this case, 11 Bytes could account for less than 11 characters.

Also those field types might be treated differently in regard to accented characters or case, for example 'binaryField(ete) = "été"' will not match while 'charField(ete) = "été"' might (again not sure about Oracle).

Answer 3

Let us assume the database character set is UTF-8, which I believe is the default in recent version of Oracle. In this case, some characters take more than 1 byte to store in the database.

If you define the field as VARCHAR2(11 BYTE), Oracle will allocate 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store, e.g. non-English characters.

By defining the field as VARCHAR2(11 CHAR) you tell Oracle to allocate enough space to store 11 characters, no matter how many bytes it takes to store each one. I believe that in Oracle 10g, 3 bytes per character were used.

Answer 4

what version of oracle? In general, a character may take more than one byte, Google for unicode. Here's a start:

http://www.oracle.com/technology/oramag/oracle/03-nov/o63tech_glob.html

Answer 5

Depending on the system configuration, size of CHAR mesured in BYTES can vary. In your examples:

1. Limits field to 11 BYTE
2. Limits field to 11 CHARacters

---

Conclusion: 1 CHAR is not equal to 1 BYTE.