The two datatypes, char and varchar are used in SQL (structured query language) to store character strings of fixed and variable lengths respectively. SQL is a standard language that is used for accessing and manipulating databases. Let us see what are the differences between these two datatypes.
Char vs Varchar
The basic difference between Char and Varchar is that:
char stores only fixed-length character string data types whereas varchar stores variable-length string where an upper limit of length is specified.
1.Char(n) datatype
Char is a data type in SQL that can store characters of a fixed length. What is a datatype? A data type defines the type of data that a declared variable can hold. Data could be a string of single-byte or multibyte numbers, letters, numbers or any other characters that are supported by the database locale. It uses static memory location.
Ø Storage size – N bytes (set length)
Example:
Code –
See AlsoString Limitations - Open Database Connectivity (ODBC)MySQL data types: VARCHAR and CHAR — PlanetScaleString & Binary Data TypesSnowflake CommunityCREATE TABLE Employee(Name VARCHAR(20), Designation CHAR(20));
SELECT LENGTH(Designation) FROM Employee;
Output –
20
2. Varchar(n) datatype
Varchar is a datatype in SQL that holds characters of variable length. This data type stores character strings of up to 255 bytes in a variable-length field. The data can consist of letters, numbers, and symbols. It uses dynamic memory location.
Ø Storage size – n bytes + 2 bytes
Example:
Code –
CREATE TABLE Employee(Name VARCHAR(50), Designation CHAR(20));
INSERT into Employee VALUES('Phoebe', ‘Software Developer');
SELECT LENGTH(Name) FROM Employee;
Output –
6
Differences between Char & Varchar
| Char | Varchar |
| It is an abbreviation for characters. | It is an abbreviation for variable characters. |
| Char datatype is used to store character strings of fixed length. | Varchar datatype is used to store character strings of variable length. |
| It uses static memory location. | It uses dynamic memory location. |
| Char takes 1 byte space for each character. | Varchar take 1 byte for each character along with some extra bytes to store length information. |
| We can use char datatype when we know the length of the string. | We can use it when we are not sure of the length of the string. |
| Char datatype can be used when we expect the data values in a column to be of same length. | Varchar datatype can be used when we expect the data values in a column to be of variable length. |
What are some more SQL datatypes?
When we declare a variable, we have a purpose to store a specific type of value in it. For example, a numeric value, alphabetical value, decimal value, etc. Each column and variable have a related data type in SQL server. We can divide all datatypes into 7 major categories such as,
- Exact numeric data type: bit, tinyint, int, bigint, decimal, numeric, money, smallmoney and smallint.
- Approximate numeric data type:Read and float.
- Date and time data type:date, DateTime, datetime2, datetimeoffset, smalldatetime, time.
- Character strings data type: char, varchar, text.
- Unicode character strings data type: Nchar, Nvarchar, Ntext.
- Binary strings data type: Binary, image and varbinary (stores binary byte strings rather than non-binary character strings)
- Other data types: Cursor, hierarchy id, SQL variant, table, row version, unique identifier, XML, etc.
Conclusion
Even though varchar seems to be a better option, it is not wise to use it unless it is absolutely necessary. Char datatype allocates only as much memory as required, whereas varchar allocates extra spaces that many times go waste and occupy space unnecessarily. Hence, make sure to use the memory spaces wisely for better and optimized performance.
You may also be interested in reading:
As an enthusiast in databases and SQL, I've worked extensively with various datatypes, including char and varchar. The differences between these two are fundamental in structuring data efficiently within SQL databases. Char stores fixed-length strings, while varchar accommodates variable-length strings with a specified upper limit. This distinction impacts memory allocation and storage.
The char datatype, defined as Char(n), reserves a set length in memory for character strings. For instance, when creating a table, specifying CHAR(20) for a column reserves 20 bytes for each value, ensuring uniformity in data storage.
On the other hand, the varchar datatype offers flexibility by storing variable-length strings, using memory dynamically based on the actual length of the stored data. For instance, VARCHAR(50) allows up to 50 characters, but it allocates only the necessary bytes plus an extra 2 bytes for length information.
Key points of difference between char and varchar:
- Char stores fixed-length strings; varchar stores variable-length strings.
- Char uses static memory allocation; varchar uses dynamic memory allocation.
- Char allocates 1 byte per character; varchar does too but with additional bytes for length information.
- Use char when the string length is known and uniform; use varchar for variable-length data.
Expanding on SQL datatypes: SQL encompasses various datatypes categorized into seven major groups:
- Exact numeric data types (e.g., int, decimal, money)
- Approximate numeric data types (e.g., float, real)
- Date and time data types (e.g., date, datetime, time)
- Character strings data types (e.g., char, varchar, text)
- Unicode character strings data types (e.g., nchar, nvarchar, ntext)
- Binary strings data types (e.g., binary, varbinary, image)
- Other data types (e.g., cursor, XML, unique identifier)
Regarding the conclusion of the article, the preference between char and varchar often depends on specific data requirements. While varchar seems more flexible, it's not advisable to use it indiscriminately due to potential wasted memory. Char's efficient memory allocation makes it suitable for uniform data lengths, optimizing performance.
If you're keen on exploring more topics, I could delve into Black Box Testing versus White Box Testing or discuss pointers on crafting a resume that stands out to HR professionals. Additionally, if programming piques your interest, I can recommend free online courses from prestigious universities that offer certifications in programming.
