Data Types - Answers 2 of 4 (varchar2)
Way (the heck) back in Oracle 6, that’s 1989 and 5 major releases for those of you who don’t eat, breath, sleep database stuff. Oracle introduced two data types, the varchar and varchar2.
The only differences were that the varchar data type was implemented for ANSII standards and it was limited to 2000 bytes. Subsequent releases of Oracle completely replaced the varchar by aliasing it to the varchar2 type.
I have heard all sorts of oracle-wives-tales on char and varchar speed advantages verses varchar2. I currently don’t believe any of them.
Rant
Databases tend to be highly technical. For this reason many people are scared by the technology and turn off their brains, expecting the guru to get the work done. Many self-proclaimed guru’s have taken advantage of this paranoia, creating some kind of mysticism around what databases can or can’t do. They pick and choose what they want to support and dismiss new features by stating that “back in version x feature y was a bad thing.” Said behavior has been very detrimental to our industry and should be shunned/nipped-in-the-bud when it rears its ugly head...
End Rant
Back to the varchar2 data type. It only takes one argument representing the maximum number of bytes or characters the field should hold. This argument is required and must be at least 1. Its maxim size is 4000 characters.
SQL> create table varchar_type (
2 column_1 varchar(10 char),
3 column_2 varchar(10),
4 column_3 varchar2(10 char), -- for utf8 stuff
5 column_4 varchar2(10)
6 );
Table created.
SQL> create table funky_varchar (
2 column_f varchar2(0)
3 );
column_f varchar2(0)
*
ERROR at line 2:
ORA-01723: zero-length columns are not allowed
The varchar and varchar2 data types, for the moment, are exactly the same; however, Oracle strongly cautions against using the varchar type as “(it) is currently synonymous with varchar2, (but) is scheduled to be redefined” (Oracle Database SQL Language Reference, #B28286-01 July 2007).
Finally, the variable character type will efficiently store data, using only the space needed, unlike the char data type which right-pads (appends “ “) to the information it holds.
SQL> create table test_character (
2 column_1 char(10),
3 column_2 varchar2(10)
4 );
Table created.
SQL> insert into test_character values ('Red','Red');
1 row created.
SQL> insert into test_character values ('Blue','Blue');
1 row created.
SQL> insert into test_character values ('Yellow','Yellow');
1 row created.
SQL> commit;
Commit complete.
SQL> select column_1,
2 length(column_1) size_char,
3 column_2,
4 length(column_2) size_varchar2
5 from test_character;
COLUMN_1 SIZE_CHAR COLUMN_2 SIZE_VARCHAR2
---------- ---------- ---------- -------------
Red 10 Red 3
Blue 10 Blue 4
Yellow 10 Yellow 6
Notice that the char data type is always 10. You can chew up a-whole-lotta space, real-fast, using the char type. It is antiquated and should not be used. I have only found one good use for it and that is to hold “Y” and “N” flag type information.
--example of char use
create table family (
person_id number(30),
father_id number(30),
mother_id number(30),
first_name varchar2(50 char),
middle_name varchar2(50 char),
last_name varchar2(50 char),
other_name varchar2(50 char),
birth_date date,
death_date date,
...
sex char, -- M or F
update_ts timestamp
);
create table employee (
employee_id number(10),
manager_id number(10),
department_id number(10),
first_name varchar2(50 char),
middle_name varchar2(50 char),
last_name varchar2(50 char),
date_hired date,
...
termination_flag char, -- Y or N
update_ts timestamp
);
