What is the difference between CHAR and VARCHAR2 data types in Oracle databases?

CHAR and VARCHAR2 are both data types used to store character strings in Oracle databases, but they have some fundamental differences in terms of storage, performance, and functionality.

The main difference between CHAR and VARCHAR2 is the way they handle variable-length character strings. CHAR is a fixed-length data type, which means that it always allocates the same amount of storage for each string value. For example, if you define a CHAR(10) column, each value in that column will always take up exactly 10 bytes of storage, regardless of whether the actual string value is shorter or longer than 10 characters.

On the other hand, VARCHAR2 is a variable-length data type, which means that it allocates only as much storage as the actual string value requires. For example, if you define a VARCHAR2(10) column and insert a string value of 5 characters, it will only take up 5 bytes of storage. This makes VARCHAR2 a more efficient option when storing strings of varying lengths.

Another difference between CHAR and VARCHAR2 is their behavior when storing and retrieving data. Because CHAR is a fixed-length data type, it pads out shorter string values with spaces to fill the allocated storage space. For example, if you insert a string value of only 5 characters into a CHAR(10) column, it will be padded with 5 spaces to fill the remaining 5 bytes of storage.

This behavior can be useful in some cases, but it can also lead to unnecessary storage consumption and slower performance when dealing with large amounts of data. In contrast, VARCHAR2 does not pad out shorter values with spaces, which can improve performance and reduce storage requirements.

Here’s a simple example to illustrate the difference between CHAR and VARCHAR2 in storing and retrieving data:

CREATE TABLE my_table (
    char_col   CHAR(10),
    varchar_col VARCHAR2(10)
);

INSERT INTO my_table VALUES ('hello', 'hello');

If we query the table now, we will get the following results:

SELECT char_col, varchar_col FROM my_table;

CHAR_COL   VARCHAR_COL
---------- ----------
hello      hello

As we can see, the value stored in the CHAR column is padded with spaces to fill the allocated 10 bytes of storage, while the value stored in the VARCHAR2 column only takes up as much space as the actual string value requires.

In conclusion, the main differences between CHAR and VARCHAR2 in Oracle databases are related to their storage, performance, and functionality. While CHAR is a fixed-length data type that pads out shorter values with spaces, VARCHAR2 is a variable-length data type that allocates only as much storage as the actual value requires. Choosing the appropriate data type depends on the specific requirements of your application and data model.