This tutorial will go over each data type and outline the difference.
The SQL Char Type
The char type refers to a data type used to store character strings of a fixed length.
The char type allows you to specify a specific size for the data you insert in that row.
Although the implementation of the char type may vary slightly according to the database engine, in Standard SQL, the char type can hold up to a maximum of 8000 non-Unicode characters.
To define a length of a char type, you can use the syntax:
Where n refers to the number (length) of characters you can store.
Since the char type is of fixed length, if you insert a string with fewer characters than the specified size, the string is padded with extra spaces to match the set string.
Similarly, if you insert a string of greater length than the set size, the string is truncated to the set size.
To illustrate, start by creating a sample database:
NOTE that in this example, we use PostgreSQL, but you can choose any SQL flavor.
Create a sample database:
Connect to the database.
Create a table with two rows. The first should be an id with auto-increment (serial) and the other of char type.
Note that the max length of the full_name column is 15.
Let us now add some sample data.
insert into char_v_varchar(full_name) values('Aaron Wise jack'); -- 15 characters
insert into char_v_varchar(full_name) values('Aaron Wise another name blah blah'); -- more than 15 characters
Pay attention to the three insert statements. The first inserts a value of 10 characters, the second adds a value of exactly 15 characters, and the other adds a value of characters greater than 15.
The first value will be padded with 5 more space characters to max the set length if we run the code above.
The second will fit perfectly, and the third will be truncated.
Adding a value greater than the set length is prohibited in some database engines. For example, in PostgreSQL, the second insert statement will fail with an error:
SQL state: 22001
To check the length of the records, we can run the query:
The code above should return the length of each row in the table:
--------
15
15
(2 rows)
NOTE that in some database engines may allow you to add a value of smaller length without padding with extra spaces.
It is good to remember that the char type uses 1 byte for one character inserted.
The SQL VARCHAR Type
Unlike the char, varchar, or variable character type, stores character strings of varying lengths.
This means you can define the size of the varchar column. However, you can add values of size greater than or less than the set length.
Let us take an example below:
In this case, we define a table with the column of varchar. Since the size of varchar can vary, we do not need to set a length.
We can then insert three values of varying size as:
insert into char_v_varchar(full_name) values('Aaron Wise jack'); -- 15 characters
insert into char_v_varchar(full_name) values('Aaron Wise another name blah blah'); -- more than 15 characters
select length(full_name) from char_v_varchar;
The code above should insert three records and return the size of each.
Final Thoughts
This then is on the difference between a char and varchar type. A char holds character strings of fixed size, while a VARCHAR holds a character string of varying lengths.
Although the VARCHAR may seem applicable for most situations, it can incur significant performance hits if used unreasonably. Therefore, if performance is a critical factor for your database, consider using a CHAR, unless necessary.
