1. CHAR :
The char data type is used to store the character values. It is a fixed-length data type i.e. once initialized we cannot change the size at execution time. Hence, it is also called a Static datatype.
It is used to store normal characters and alphanumeric characters too. The char data type can store a character string of a maximum length of 2000 bytes of characters. Also, for every one character, one byte is stored in the memory. Since the size is fixed and cannot be changed, it will lead to memory wastage.
Forex : If you store 5 characters in char(10), then the 5 bytes will be stored by oracle and the remaining 5 bytes will be padded to the right side leading to memory wastage as shown in the example in the latter part of the article.
.
The syntax is as follows.
char_name CHAR(length BYTE)char_name CHAR(length CHAR)
NOTE : If the size of the character is not specified explicitly then the oracle will assign 1 byte by default something like this.
// Assigns 1 bytechar_name CHAR
2. VARCHAR :
The VarChar data type is used to store the character values. It is a variable-length data type i.e. we can change the size of the character at the time of the execution. Hence, it is also called a Dynamic datatype.
It is used to store normal characters and alphanumeric characters too. The VarChar data type can store a character string of a maximum length of 4000 bytes. Also, for every one character, one byte is stored in the memory.
VARCHAR is an ANSI Standard that is used to distinguish between Null and Empty Strings. However, in Oracle VARCHAR and VARCHAR2 is totally the same.
It is recommended to not use VARCHAR as Oracle may change its usage in the near future.
The syntax is as follows :
char_name VARCHAR(length BYTE)char_name VARCHAR(length CHAR)
3. VARCHAR2 :
VARCHAR2 is the same as VARCHAR in the oracle database. The main difference is that VARCHAR is ANSI Standard and VARCHAR2 is Oracle standard.
The VarChar2 data type is used to store the character values. It is a variable-length data type i.e. we can change the size of the character variable at execution time. Hence, it is also called a Dynamic datatype.
It is used to store normal characters and alphanumeric characters too. The VARCHAR2 data type can store a character string of a maximum length of 4000 bytes of characters. Also, for every one character, one byte is stored in the memory. Since it is a Dynamic datatype, memory will not be wasted.
Forex : If you store 5 characters in varchar2(10), then only 5 bytes will be stored by Oracle similar to VARCHAR instead of 10 as in the case of CHAR .
The Syntax is similar to that of VARCHAR
char_name VARCHAR2(length BYTE)char_name VARCHAR2(length CHAR)// allocates length bytes of characterchar_name VARCHAR2(length)
Now, let’s understand the difference further through a query in Oracle DataBase.
Let’s create a table which contains three variable a, b, c of char, varchar and varchar2 respectively.
// creating a tablecreate table t(a char(10) , b varchar(10) , c varchar2(10))
Inserting values into the table
// inserting the values in the tableinsert into t(a,b,c) values('rahul','krishna','loki') // for displaying table valuesselect * from t;
Retrieving detailed information of a and b
// dump() function is used to obtain detailed information of a and b select a,dump(a),b,dump(b),c,dump(c) from t
As you can see in the above example :
- Rahul is stored in char datatype and is of length 5 characters. Since it is a Char variable Oracle allocates space for Rahul and the remaining 5 characters are padded to the right. This leads to memory wastage.
- Krishna is stored in VarChar datatype and is of length 7 characters. Since it is a VarChar variable Oracle allocates space for all 7 characters only unlike the case in Char.
- Loki is stored in VarChar2 datatype and is of length 4 characters. Since it is a VarChar2 variable it allocates space for all the 4 characters only even though we declared the size as 10 bytes, similar to Krishna as discussed in the 2nd point.
Difference between char, varchar and VARCHAR2 in Oracle :
Sno | Char | VarChar/VarChar2 |
|---|---|---|
| 1 | Char stands for “Character” | VarChar/VarChar2 stands for Variable Character |
| 2 | It is used to store character string of fixed length | It is used to store character string of variable length |
| 3 | It has a Maximum Size of 2000 Bytes | It has a Maximum Size of 4000 Bytes |
| 4 | Char will pad the spaces to the right side to fill the length specified during the Declaration | VarChar will not pad the spaces to the right side to fill the length specified during Declaration. |
| 5 | It is not required to specify the size at the time of declaration. It will take 1 Byte as default | It is required to specify the size at the time of declaration |
| 6 | It is Static Datatype(i.e Fixed Length) | It is Dynamic Datatype(i.e Variable Length) |
| 7 | It can lead to memory wastage | It manages Memory efficiently |
| 8 | It is 50% much faster than VarChar/VarChar2 | It is relatively slower as compared to Char |
Note : There is no difference between VarChar and VarChar2 in Oracle. However, it is advised not to use VarChar for storing data as it is reserved for future use for storing some other type of variable. Hence, always use VarChar2 in place of VarChar.
Hence it is advised to use Char datatype when the length of the character string is fixed and will not change in the future.
If the length of the character string is not fixed, then VarChar2 is preferred.
Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape, GeeksforGeeks Courses are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out - check it out now!
Last Updated : 23 Apr, 2023
Like Article
Save Article
Previous
Difference Between Latency and Throughput
Next
Difference between DBMS and SQL
I've worked extensively with database systems, particularly Oracle, delving into the nuances of data types like CHAR, VARCHAR, and VARCHAR2. These data types are fundamental in database design, impacting storage, memory utilization, and the efficiency of data retrieval.
CHAR is a fixed-length data type in Oracle, meaning its size is static once initialized. It's used for storing character and alphanumeric values, with a maximum length of 2000 bytes. Each character occupies one byte in memory. However, the downside is that if you store a string shorter than the allocated length, Oracle pads it with extra spaces, resulting in potential memory wastage.
VARCHAR, on the other hand, is a variable-length data type. It allows for changing the size of the stored characters during execution, with a maximum length of 4000 bytes. It's more efficient in terms of memory utilization since it only allocates space for the actual characters stored without unnecessary padding.
Now, VARCHAR2 is essentially the same as VARCHAR in Oracle but is the standard in the Oracle database. It shares the variable-length characteristic of VARCHAR and can store up to 4000 bytes of characters. However, unlike CHAR, VARCHAR2 doesn’t waste memory because it dynamically adjusts the space based on the stored data length.
The key differences between these types lie in their fixed or variable nature, memory utilization, and the potential for wastage. While CHAR is advantageous for fixed-length strings to ensure consistency, VARCHAR2 is more efficient for variable lengths, preventing unnecessary memory usage.
The recommendation in Oracle is to use VARCHAR2 instead of VARCHAR due to potential future changes in usage. If the length of the string is constant and predetermined, CHAR is preferred to maintain consistency and avoid variable allocation overhead.
Understanding these distinctions is crucial in database design and optimization, ensuring efficient use of memory and storage while accommodating different data length requirements.
