top of page
Search

What is VARCHAR in SQL?

If you are just starting to explore SQL, you may be wondering, "What is VARCHAR in SQL, and how does it stand apart from other character data types such as CHAR?" If this question has crossed your mind, you've come to the right place!


In this article, we will explore the definition of VARCHAR, how it stores data, its advantages, use cases, and the key distinctions between CHAR and VARCHAR in SQL. Whether you're building a small-scale application or managing complex enterprise-level databases, learning the complexness of VARCHAR will definitely enhance your ability to handle textual data effectively.


Table of content

What is VARCHAR in SQL?

VARCHAR is a data type in SQL that stores character strings of variable length. The maximum length of a VARCHAR column is specified when the column is created. VARCHAR columns can store up to 8,000 characters in most SQL databases.


Below are the best features that are offered by VARCHAR in SQL:

  • The storage size of a VARCHAR column is equal to the actual length of the string in bytes, plus 2 bytes for the length information.

  • VARCHAR columns are not as efficient as CHAR columns when the actual data length is equal to or greater than the maximum length.

  • The maximum length of a VARCHAR column can be specified as a number from 1 to 8,000.

  • The default maximum length for a VARCHAR column is 255 characters.


When to use VARCHAR?

  • When you need to store strings of variable length.

  • When you know that the actual data length will be less than the maximum length.

  • When you want to save storage space because VARCHAR columns only use the amount of storage space necessary to store the value.

When not to use VARCHAR?

  • When you need to store strings of fixed length.

  • When you know that the actual data length will be equal to or greater than the maximum length.

  • When you need to optimize performance because VARCHAR columns are not as efficient as CHAR columns when the actual data length is equal to or greater than the maximum length.


Difference between CHAR and VARCHAR in SQL

In SQL, both CHAR and VARCHAR are used to store character data, but they have some key differences in how they handle and store the data. The main distinction lies in the way they allocate memory for storing values.


CHAR:

  • CHAR is a fixed-length data type, which means it always reserves a specific amount of memory for each value, regardless of the actual data length.

  • When you define a column as CHAR(20), it will always occupy 20 characters' worth of storage, even if the actual data you store in that column is shorter.

  • If you store a string with fewer than 20 characters, it will be padded with spaces to fill the entire 20-character space.

  • Due to fixed-length storage, CHAR is generally more suitable for columns with a consistent data length, like codes or identifiers, where the data length is predictable and constant.


VARCHAR:

  • VARCHAR stands for "variable-length character," and it only uses the memory necessary to store the actual data, plus a small overhead to store the length of the data.

  • When you define a column as VARCHAR(20), it will use as much memory as needed to store the data, up to a maximum of 20 characters.

  • If you store a string with fewer than 20 characters, it will not be padded with spaces; instead, it will only take the required amount of space.

  • As a result, VARCHAR is more flexible and efficient for storing variable-length data, as it optimizes memory usage and doesn't waste space on padding.

Memory Usage and Efficiency:

  • For CHAR, memory usage is fixed and constant. If you have a CHAR(20) column and store a 10-character value, it will always use 20 characters' worth of memory.

  • For VARCHAR, memory usage depends on the actual length of the data. Storing the same 10-character value will only use 10 characters' worth of memory.


Example:

Let's create two tables, one with CHAR(20) and the other with VARCHAR(20), and then compare their behavior:


Create table with CHAR(20)

-- Create a table with a CHAR column
CREATE TABLE char_table (   
    ID INT NOT NULL AUTO_INCREMENT,   
    Name CHAR(20) NOT NULL,   
    PRIMARY KEY (ID) 
);  

-- Insert a row into the table
INSERT INTO char_table (Name) VALUES ('Rahul Roy');  

-- Select the row from the table
SELECT * FROM char_table;  

-- The output of the SELECT statement will be:
-- ID | Name
-- 1 | Rahul Roy

Create table with VARCHAR(20)

-- Create a table with a VARCHAR column
CREATE TABLE varchar_table (   
    ID INT NOT NULL AUTO_INCREMENT,   
    Name VARCHAR(20) NOT NULL,   
    PRIMARY KEY (id) 
);  

-- Insert a row into the table
INSERT INTO varchar_table (Name) VALUES ('Rahul Roy');  

-- Select the row from the table
SELECT * FROM varchar_table;  

-- The output of the SELECT statement will be:
-- ID | Name
-- 1 | Rahul Roy

In the above code, the CHAR column has a maximum length of 20 characters, while the VARCHAR column has a maximum length of 20 characters.


The code then inserts a row into each table with the value "Rahul Roy". The output of the SELECT statement shows that the CHAR column stores the value "Rahul Roy" as a 20-character string, while the VARCHAR column stores the value "Rahul Roy" as a 5-character string.


Conclusion

VARCHAR is a versatile character data type in SQL. It has the ability to store variable-length data efficiently making it a powerful choice for managing various text content in databases. Happy querying!

0 comments
bottom of page