What is the difference between VARCHAR, VARCHAR2 and CHAR data types?

Submitted by admin on Sat, 2005-11-26 08:30
Body: 

Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used:

CHAR

CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.

SQL> CREATE TABLE char_test (col1 CHAR(10));

Table created.

SQL> INSERT INTO char_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM char_test;

COL1       LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty               10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32

Note: ASCII character 32 is a blank space.

VARCHAR

Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.

SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));

Table created.

SQL> INSERT INTO varchar_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar_test;

COL1       LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty                6 Typ=1 Len=6: 113,119,101,114,116,121

VARCHAR2

VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.

SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));

Table created.

SQL> INSERT INTO varchar2_test VALUES ('qwerty');

1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar2_test;

COL1       LENGTH(COL1) ASCII Dump
---------- ------------ ------------------------------------------------------------
qwerty                6 Typ=1 Len=6: 113,119,101,114,116,121

»

Comments

Submitted by Awadhesh Kumar ... (not verified) on Thu, 2006-06-15 07:58.

ADMIN WARNING: SOME OF THE BELOW COMMENTS, LIKE THIS ONE, CONTAIN WRONG INFORMATION!

1. VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring datatype.

2. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters.

3. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space.

»

Submitted by Yogesh Kumar (not verified) on Mon, 2006-11-20 23:38.

VARCHAR is used to store variable length character strings up to 4000 characters. But, remember CHAR is faster than VARCHAR - some times up to 50% faster.

»

Submitted by William Robertson on Tue, 2007-07-31 17:17.

1. VARCHAR is NOT going to be replaced by VARCHAR2 in the next version. It is reserved for redefinition in some future version, probably well beyond 11g, if ever. This warning has been around since the two types were first introduced in Oracle 6 around 1989. It is there to allow Oracle some flexibility should the need arise.

2. The two types are currently synonymous, and so the storage limitations are identical.

3. As for #2, there is no difference between VARCHAR and VARCHAR2 in handling of NULL values because they are currently the same type.

While I'm at it, VARCHAR2 does NOT contain an additional length indicator (as the DUMP output clearly shows), CHAR is NOT faster than VARCHAR2, and VARCHAR does NOT use space any differently to VARCHAR2 and never has done. There is no good reason to use CHAR for anything, even fixed-length keys, and there never has been. I've never seen so much complete rubbish on one page in my life.

»

Submitted by Kalyani P. Banerjee (not verified) on Tue, 2006-07-25 01:42.

[i].
CHAR(5) is fixed length, right padded with spaces.
VARCHAR(5) is fixed length, right padded with null
VARCHAR2(5) is variable length.

Thus the difference between VARCHAR and VARCHAR2 is that VARCHAR is ANSI standard but takes up space whereas VARCHAR2 is Oracle-only but makes more efficient use of space.

But as someone has already pointed out, Oracle have resolved this (in 9.2, maybe earlier) by casting VARCHAR to VARCHAR2.

[ii].

Varchar and Varchar2 both are of variable character. Varchar can have MAximum 2000 character while Varchar can contain maximum 4000 character.

[iii]
Varchar is of ANSI SQL standart while Varchar2 is of Oracle standard.

»

Submitted by Princevel Pereira (not verified) on Thu, 2006-12-07 01:35.

Searching is faster in CHAR as all the strings are stored at a specified position from the each other, the system doesnot have to search for the end of string.
Whereas in VARCHAR the system has to first find the end of string and then go for searching.

»

Submitted by Adilz (not verified) on Mon, 2007-01-01 22:59.

VARCHAR was previosuly of fixed length and right padded with NULL spaces to fill up the total space. However, it seems that Oracle has resolved this issue and has made VARCHAR the same as VARCHAR2. Now, only CHAR is able of fixed length storage.

VARCHAR2 is variable length - according to the values that will be stored in the database.

Thanks
Adilz

»

Submitted by Andrew York (not verified) on Tue, 2007-01-16 16:02.

Varchar and varchar2 are synonyms (now at least) varchar is supposed to be removed from oracle sql, but that will probably never happen. They both use 0 to N characters up on the disk. Depending on your NLS_CHARACTER_FORMAT (its called something like that) each character can take more than one byte on disk. ASCII uses one byte per character. UTF8 I think uses one byte unless the first bit is 1 then it may add another byte (and keep doing so until the first bit of the new byte is not 1) Most other forms of unicode default to two bytes. They can all grow I believe

char() should be thought of as fixd length. It will always take up the same amount of space no matter what is really stored in the column. It is a good idea to use them on fixed length things like MD5 signatures.

Varchars should fragment the datafile if their contents change. If they don't fragment the datafile then Oracle is taking extra steps to prevent this. So IF you are working with a string that will always be the same length (and that is less than 255) the go with char(). Where your string is < 4000 use Varchar2. Anything else go with CLOB.

»

Submitted by vnomat on Mon, 2009-03-09 10:05.

I don't feel compelled to comment on varchar. Never used it in Oracle and probably never will.

However I am compelled to comment about char and varchar because of some unhelpful comments here. First of all it is easy enough to verify stuff in Oracle so it comes as a surprise at some of the comments here.

char and varchar properties and length restrictions are widely available, however what is not available widely is perhaps the performance issue. I decided to put it to rest by creating a table of a million rows with essentially two columns (c char(6), vc varchar2(10)) and filled each with same values of 6 characters long (comparing 'apples' to 'apples') from a random table in my app.

I issued a search: select count(*) from mytable where c = 'values' and alternatively vc = 'values'
Invariably the char (c) search performed almost half as fast as the varchar2 (vc) search.

Varchar2 performed much better. Don't take my word for it, try it for yourself.

Vinny

»

Submitted by jpsaccount on Thu, 2010-08-05 09:10.

Interesting, I want to try this out myself.
I especially find it worthy of further analysis given that Tom has a diametrically opposite view in his site.

»

Submitted by Michel Cadot on Thu, 2010-08-05 03:37.

You will find a comment on this page by Tom Kyte at:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476

Michel

»

Submitted by Frank Naude on Wed, 2016-06-08 04:53.

Good to read Tom's view on this. However, it sounds like he's criticising this article, while he should really address the silly comments left by users.

»