Database Reference
In-Depth Information
data
11 el
H
l o Wor
l
d
Length
Byte
Figure 12-1.
Hello World stored in a VARCHAR2(80)
A
CHAR(80)
holding the same data, on the other hand, would look like Figure
12-2
.
data
80
H
el
l
o
Wo r
l
d
Length
Byte
69 spaces
Figure 12-2.
Hello World stored in a CHAR(80)
The fact that a
CHAR
/
NCHAR
is really nothing more than a
VARCHAR2
/
NVARCHAR2
in disguise makes me of the
opinion that there are really only two character string types to ever consider, namely
VARCHAR2
and
NVARCHAR2
. I have
never found a use for the
CHAR
type in any application. Since a
CHAR
type
always
blank pads the resulting string out
to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index
segments. That would be bad enough, but there is another important reason to avoid
CHAR
/
NCHAR
types: they create
confusion in applications that need to retrieve this information (many cannot “find” their data after storing it). The
reason for this relates to the rules of character string comparison and the strictness with which they are performed.
Let's use the
'Hello World'
string in a simple table to demonstrate:
EODA@ORA12CR1> create table t
2 ( char_column char(20),
3 varchar2_column varchar2(20)
4 )
5 /
Table created.
EODA@ORA12CR1> insert into t values ( 'Hello World', 'Hello World' );
1 row created.
EODA@ORA12CR1> select * from t;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
