What is the difference between CHAR and VARCHAR2 in SQL?

Estimated reading time: 2 minutes

In SQL, both CHAR and VARCHAR2 are character data types, but they have some differences in terms of their storage and usage.

CHAR:

The CHAR data type is a fixed-length character data type.
When you define a column as CHAR, you must specify a fixed length for the data, which is the number of characters that the column can store.
If you insert a string into a CHAR column that is shorter than the specified length, it will be padded with spaces to fill the remaining length.
The space padding can take up extra storage space, which can be a disadvantage in cases where storage efficiency is important.
If you try to do some comparisons, the extra spaces may not yield a match for you.

VARCHAR2:

The VARCHAR2 data type is a variable-length character data type.
When you define a column as VARCHAR2, you do not need to specify a fixed length. Instead, the length of the data can vary based on the content of the column.
VARCHAR2 columns only take up as much space as is needed to store the actual data, which can be an advantage for storage efficiency.
If you insert a string into a VARCHAR2 column that is shorter than the maximum length, no padding occurs.

Here’s an example to illustrate the difference:

CREATE TABLE char_test (
   id NUMBER,
   name CHAR(10),
   address CHAR(20)
);

CREATE TABLE varchar2_test (
   id NUMBER,
   name VARCHAR2(10),
   address VARCHAR2(20)
);

INSERT INTO char_test (id, name, address) VALUES (1, 'John', '123 Main St');

INSERT INTO varchar2_test (id, name, address) VALUES (1, 'John', '123 Main St');

In the char_test table, the name and address columns are both defined as CHAR, with fixed lengths of 10 and 20 characters, respectively.

If we insert the name “John” and the address “123 Main St” into the char_test table, the name will be padded with spaces to fill the entire 10-character length of the column, while the address will be padded with spaces to fill the entire 20-character length.

In the varchar2_test table, the name and address columns are both defined as VARCHAR2, with maximum lengths of 10 and 20 characters, respectively.

If we insert the same data into the varchar2_test table, the columns will only take up the actual amount of space needed to store the data, without any padding.

CHAR:

VARCHAR2:

Related Posts