Difference Between Char and Varchar in SQL

Char and Varchar are both datatypes in SQL. They are widely used for storing character strings of different lengths. For efficient data storage, management, and retrieval of data from DBMS, the usage of data types is important to learn. In this article, we will learn the differences between the CHAR and VARCHAR data types in SQL.

We’ll see how CHAR and VARCHAR differ in storage usage, features, and other things. Along with this, we will see their syntaxes, different examples, and output. Let’s get started.

What is the CHAR Data type in SQL?

The CHAR datatype stores the fixed-length characters of strings. It uses a fixed amount of storage space to store the string characters. Once you define a column with CHAR, that column will reserve a fixed length of space in that column. In CHAR, the values are padded with spaces to the specified length. CHAR is best used when the data values in a column are expected to be the same length.

Features

• Fixed Length: The CHAR data type uses the fixed length of storage in the column. If the length of characters is less, the extra space is filled with the spaces.
• Storage efficient: With CHAR, the overhead of variable length is removed as the size of data is fixed all the time in the database.
• Performance: Predictable data alignment and proper indexing can be achieved if the columns are defined using the CHAR data type, which will ultimately provide better performance.

Syntax

CREATE TABLE my_table (

my_column1 CHAR(n)

);

In the given syntax, we are creating a table (my_table) with the column (my_column1) using the CHAR datatype of n length. Here, n defines the length of characters to be stored in the column.

For example, if we create a CHAR of n = 500 size, then the storage space will occupy the 500 bytes on the disk. And let’s say, if the space of 300 bytes is only used by a column, then the leftover 200 bytes will contain the spacing. This leads to a waste of space.

Code Example 1: The below code demonstrates how to create a table with CHAR datatype.

CREATE TABLE University (

StudentID CHAR(8),

StudentFirstName CHAR(35),

StudentLastName CHAR(20),

StudentDept CHAR(40),

StudentPhone CHAR(10)

);

Explanation:

In this example, we are creating a university table with the columns, StudentID of size 8, StudentFirstName of size 35, StudentLastName of size 20, StudentDept of size 40, and StudentPhone of size 10 using fixed length of size.  Here, each CHAR column reserves a fixed amount of storage space regardless of the actual data length. For instance, StudentID will always occupy 8 characters, with trailing spaces if required.

Code Example 2: The below code demonstrates how to insert data in a table with CHAR datatype.

INSERT INTO University (StudentID , StudentFirstName , StudentLastName , StudentDept , StudentPhone)

VALUES (‘01234’, ‘Rajesh’, ‘XYZ’, ‘CSE’, 8988989898);

INSERT INTO University (StudentID , StudentFirstName , StudentLastName , StudentDept , StudentPhone)

VALUES (‘01235’, ‘Amit’, ‘WXY’, ‘CSE’, 8988679898);

INSERT INTO University (StudentID , StudentFirstName , StudentLastName , StudentDept , StudentPhone)

VALUES (‘01236’, ‘Johnny’, ‘JHG’, ‘CSE’, 8988459898);

Explanation:

In this example, we are inserting the 3 different data records into the university table with the columns, StudentID, StudentFirstName, StudentLastName, StudentDept, and StudentPhone using fixed length of size. Here, a column like StudentID will include the extra padding or trail spaces to fill the total length of characters defined at the time of creating the column.

Code Example 3: The below code demonstrates how to find the length of the column of the CHAR datatype.

SELECT LENGTH(StudentFirstName) FROM Student;

SELECT LENGTH(StudentLastName) FROM Student;

SELECT LENGTH(StudentPhone) FROM Student;

Output:

LENGTH(StudentFirstName)

35

35

35

Here, the StudentFirstName column returns the length of characters that are stored in the column with all trailing spaces. As we have defined the length of StudentFirstName as 35 the maximum size of the length of characters the column can include, therefore it returns the fixed length of 35 in all three records from the table.

LENGTH(StudentLastName)

20

20

20

Here, the StudentLastName column returns the length of characters that are stored in the column with all trailing spaces. As we have defined the length of StudentLastName as 20, the maximum size of the length of characters the column can include. Therefore, it returns the fixed length of 20 in all three records from the table.

LENGTH(StudentPhone)

10

10

10

Here, the StudentPhone column returns the length of characters that are stored in the column with all trailing spaces. As we have defined the length of StudentPhone as 10 the maximum size of the length of characters the column can include, therefore it returns the fixed length of 10 in all three records from the table.

Also Read: Types of SQL commands

VARCHAR, or variable character, is a data type used to store data of variable length. In VARCHAR, we define the column with a maximum size of length of characters to store. Using this data type will ensure there is no padding in the column, and the column will only occupy the space that a string needs. In other words, VARCHAR conserves space when the length of values varies but requires one byte for each character, plus a few extra bytes for length information.

Features

• Variable Length: The VARCHAR helps in storing the characters with variable length, which means if the characters are long, that much space will be occupied, and vice versa. Using VARCHAR leads to storage savings and cost savings for the companies using databases.
• No spacing: Unlike CHAR, VARCHAR does not pad the remaining space with spaces. In VARCHAR, the data is stored exactly as entered.
• Flexible: VARCHAR is well-suited for columns where the length of the data can vary significantly from one row to the next.

Syntax

CREATE TABLE my_table (

my_column1 VARCHAR(n)

);

In the given syntax, we are creating a table (my_table) with the column (my_column1) using the VARCHAR datatype of n length. Here, n defines the maximum length of characters to be stored in the column.

For example, if we create a VARCHAR of n = 400 size, then the storage space will occupy a maximum of 400 bytes on the disk, plus an additional 1 or 2 bytes for storing the length information, depending on the database system. Because the data type will use the amount of space a variable needs, it leads to saving of space on disks.

Code Example 1: The below code demonstrates how to create a table with VARCHAR datatype.

CREATE TABLE University (

StudentID VARCHAR(10),

StudentFirstName VARCHAR(35),

StudentLastName VARCHAR(20),

StudentDept VARCHAR(40),

StudentPhone VARCHAR(10)

);

Explanation:

In this example, we are creating a university table with the columns, StudentID of size 10, StudentFirstName of size 35, StudentLastName of size 20, StudentDept of size 40, and StudentPhone of size 10 using fixed length of size.  Here, each VARCHAR column allocates the storage space according to the actual length of data to be inserted. For instance, StudentID will always occupy up to a maximum of 10 characters, without any trailing spaces.

Code Example 2: The below code demonstrates how to insert data in a table with VARCHAR datatype.

INSERT INTO University (StudentID , StudentFirstName , StudentLastName , StudentDept , StudentPhone)

VALUES (‘01234’, ‘Rajesh’, ‘XYZ’, ‘CSE’, 898898698);

INSERT INTO University (StudentID , StudentFirstName , StudentLastName , StudentDept , StudentPhone)

VALUES (‘01235’, ‘Amit’, ‘WXY’, ‘CSE’, 89886798);

INSERT INTO University (StudentID , StudentFirstName , StudentLastName , StudentDept , StudentPhone)

VALUES (‘01236’, ‘Johnny’, ‘JHG’, ‘CSE’, 8988489448);

Explanation:

In this example, we are inserting the 3 different data records into the university table with the columns, StudentID, StudentFirstName, StudentLastName, StudentDept, and StudentPhone using variable length of size. Here, there will be no extra padding or trail spaces, as the data will occupy the size according to character length.

Code Example 3: The below code demonstrates how to find the length of the column of the VARCHAR datatype.

SELECT LENGTH(StudentFirstName) FROM Student;

SELECT LENGTH(StudentLastName) FROM Student;

SELECT LENGTH(StudentPhone) FROM Student;

Output:

LENGTH(StudentFirstName)

7

4

6

Here, the StudentFirstName column returns the length of characters that are stored in the column without any trailing spaces or padding. As we have defined the length of StudentFirstName as 35 the maximum size of the length of characters the column can include, but it returns only 7, 4, and 6 as the length of all three records from the table.

LENGTH(StudentLastName)

3

3

3

Here, the StudentLastName column returns the length of characters that are stored in the column without any trailing spaces or padding. As we have defined the length of StudentLastName as 20 the maximum size of the length of characters the column can include, but it returns only 3, 3, and 3 as the length of all three records from the table.

LENGTH(StudentPhone)

9

8

10

Here, the StudentPhone column returns the length of characters that are stored in the column without any trailing spaces or padding. As we have defined the length of StudentPhone as 10 as the maximum size of the length of characters, the column can be included, but it returns only 9, 8, and 10 as the length of all three records from the table.

Also Read: SQL Operators

Key Differences Between Char and Varchar in SQL

Let’s see some of the differences between the CHAR and VARCHAR data types in SQL:

Conclusion

To create effective and efficient database schemas, it is essential to know the differences between CHAR and VARCHAR in SQL. In this article, we have learned the differences between CHAR and VARCHAR data types in SQL. Depending on the type of data being saved, each format—VARCHAR for variable-length flexibility and CHAR for fixed-length storage—has unique benefits. Database managers can maximise memory use, storage capacity, and query performance by selecting the right data type and length for each column. This will ultimately improve the overall dependability and efficiency of the database system.