CHAR and VARCHAR Data Types in Different Database Engines

Patrycja Dybka, Community Manager at Vertabelo

by
Patrycja Dybka
Community Manager at Vertabelo

Posted: May 31, 2016

Storage engines can surprise you. For example, take the CHAR data type. It expects an exact number of characters and by definition stores a fixed amount of information. However, you don’t have to fill all the available CHAR space – a shorter value will work. This is so similar to VARCHAR that I decided to explore the differences between these two types.

Before diving into the details, let’s start with some basic information. CHAR and VARCHAR are SQL data types dedicated to storing character values. They are available in almost every database engine. Due to database and encoding particulars, the storage of character values in CHAR and VARCHAR columns differs.

In this article, we will look at how CHAR and VARCHAR data types behave in:

  • MySQL
  • PostgreSQL
  • SQLite
  • Oracle
  • MS SQL Server

MySQL

MySQL Reference Manual: Char and Varchar Types

CHAR columnsVARCHAR columns
LengthThe length is fixed and indicates the number of characters declared when a table is created. It can be any value from 0 to 255 bytes.The length is variable, but the maximum is specified when creating a table. Maximum lengths can range from 0 to 255 bytes (before MySQL 5.0.3) or from 0 to 65,535 bytes in later versions. If a multi-byte character set is used, the upper limit is 21,844 bytes.
StoringWhen CHAR values are stored, they are right-padded with trailing spaces to make up the specified length.VARCHAR values aren’t padded during storage. Trailing spaces are retained when data is inserted into the field.

VARCHAR values are stored as a 1-byte or 2-byte prefix plus data:
1 byte --> when the data value requires less than 255 bytes.
2 byte --> when the data value requires more than 255 bytes.
RetrievingWhen CHAR values are retrieved, the trailing spaces are removed (unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled)VARCHAR’s trailing spaces are retained during retrieval.

To demonstrate the differences between CHAR and VARCHAR, I created the following test tables:

Storing CHAR Values

ValueCHAR(4)Actual storage requiredDescription
'''    '4 bytes4 bytes for trailing spaces
'ab''ab '4 bytes2 bytes for data and 2 bytes for trailing spaces
'abcd''abcd'4 bytes4 bytes for data
'abcdefgh''abcd'4 bytes4 bytes for data *

Selecting CHAR values

length() function – returns string length in bytes.
char_length() function – returns string length in characters.

Inserted valueLength of the retrieved stringDescription
''0 bytes4 bytes (trailing spaces) are removed during retrieval
'ab'2 byte2 bytes (trailing spaces) are removed during retrieval
'abcd'4 bytes4 bytes are retrieved
'd '1 byteDuring storage, the character ‘d’ and a space was stored. Two additional spaces were added.
During retrieval, all spaces were removed (including the intentional stored space).

MySQL – Selecting CHAR values, sql server char

Storing VARCHAR values

ValueVARCHAR(4)Actual storage requiredDescription
''''1 byte1 byte for length prefix
'ab''ab'3 bytes2 bytes for data and 1 byte for the prefix
'abcd''abcd'5 bytes4 bytes for data and 1 byte for the prefix
'abcdefgh''abcd'5 bytes4 bytes for data and 1 byte for the length prefix *

Selecting VARCHAR values

Inserted valueLength of the retrieved stringDescription
''0 bytesNo data stored
'ab'2 bytes2 bytes for data
'abcd'4 bytes4 bytes for data
'd '2 bytesDuring storage, the character ‘d’ and a space was stored.
All data is retrieved (including the intentionally-stored space).

MySQL – Selecting VARCHAR values, sql varchar

* The values that exceed the column length are truncated only when SQL strict mode is disabled. Otherwise, an error occurs.

PostgreSQL

PostgreSQL Documentation: Character Types

CHAR columnsVARCHAR columns
LengthThe length is fixed and indicates the number of characters declared when a table is created.Stores variable-length strings. The max length is specified during table creation; the upper limit is about 1GB.

In the case of multibyte encodings, the maximum number of characters can be different.
StoringWhen CHAR values are stored, they are padded with trailing spaces to make up the specified length.VARCHAR values aren’t padded with extra characters.
RetrievingWhen CHAR values are retrieved, all trailing spaces are removed.Spaces are retained during the retrieval of VARCHAR values.

I calculated the number of characters/bytes in the stored string using the Postgres octet_length() and char_length() functions. To see the results, take a look at the tables and screens below.

Storing CHAR Values

ValueCHAR(4)Length of the stored stringDescription
'''    '4 bytes4 bytes for trailing spaces
'ab''ab '4 bytes2 bytes for data, 2 bytes for trailing spaces
'abcd''abcd'4 bytes4 bytes for data
'abcdefgh'Unless the excess characters are all spaces, this error will occur: “value too long for type character(4)”.

PostgreSQL – Storing CHAR Values, char data type

Storing VARCHAR Values

ValueVARCHAR(4)Length of the stored stringDescription
''''0 bytesNo additional characters are added when storing an empty value
'ab''ab'2 bytes2 bytes for data
'abcd''abcd'4 bytes4 bytes for data
'abcdefgh'Unless the excess characters are all spaces, this error will occur: “value too long for type character(4)”.

Note: CHAR(n) is usually the slowest data type (compared to VARCHAR or TEXT) because of its additional storage costs.

SQLite

Datatypes In SQLite Version 3

The SQLite database differs markedly from a traditional RDBMS. In SQLite, rather than data types there are storage classes in a manifest typing system (NULL, INTEGER, REAL, TEXT, BLOB). Data types are coerced or converted into various storage locations based on affinities (TEXT, NUMERIC, INTEGER, REAL, BLOB). The affinity of a column is determined by its declared type. If the declared type contains “CHAR”, “VARCHAR”, “TEXT” or similar types, it will have an associated TEXT affinity.

When we use the CREATE TABLE statement and we introduce VARCHAR and CHAR columns, they will be treated as TEXT by the SQLite database.

The numeric arguments in parentheses that following the type name (varchar(255)) are ignored by SQLite. SQLite does not impose any length restrictions (other than the global SQLITE_MAX_LENGTH limit) on strings, BLOBs or numeric values.

CHAR and VARCHAR columns
LengthThe numeric arguments indicating the maximum column length are ignored by the SQLite database. String lengths are governed by SQLITE_MAX_LENGTH.
StoringCHAR and VARCHAR values are converted to the TEXT type. They aren’t padded with additional characters during storage.
RetrievingSpaces are retained during retrieval.

To illustrate the differences between CHAR and VARCHAR data types in MySQL, I created two test tables with CHAR(4) and VARCHAR(4) columns. Using the SQLite length() function, I calculated the number of characters in the stored string. The typeof() function returns a string that indicates the data type.

To see the results, take a look at the tables and screens below:

SQLite – Creating table with CHAR columns, sql char


SQLite – Creating table with VARCHAR columns, varchar data type

As you see, both columns are treated the same.

ValueCHAR(4), VARCHAR(4)Length of the stored stringDescription
''''0 bytesNo additional characters are added when storing an empty value.
'ab''ab'2 bytes2 bytes for data
'abcd''abcd'4 bytes4 bytes for data
'abcdefgh''abcdefgh'8 bytes8 bytes for data

Oracle

Oracle User’s Guide: CHAR versus VARCHAR2 Semantics

CHAR columnsVARCHAR2 columns
LengthA fixed length that indicates the number of characters or bytes declared when a table is created.

The length can be any value between 1 and 2,000 bytes. The default is 1 byte.
String length is variable, but a max length is specified when a table is created.

The length can be any value between 1 and 4,000 bytes.
StoringWhen shorter CHAR values are stored, they are blank-padded to the specified length. If a CHAR value is inserted that is longer than the specified length, the insert is aborted and an error is generated.Shorter VARCHAR2 values aren’t blank-padded during storage. If a VARCHAR2 value is inserted that is longer than the specified length, the insert is aborted and an error is generated.
RetrievingWhen CHAR values are retrieved, trailing spaces are removed.Spaces are retained during retrieval.

Warning: Oracle treats empty strings as null.

To illustrate the differences between CHAR and VARCHAR data types in Oracle, I created two test tables with CHAR(4) and VARCHAR2(4) columns.

Using the Oracle length() function, I calculated the number of characters in the stored string. The lengthb() function calculates the length of stored string in bytes. To see the results, take a look at the tables and screens below.

Storing CHAR Strings

ValueCHAR(4)Length of the stored stringDescription
''nullnullEmpty string is stored as null.
'ab''ab '4 bytes2 bytes for data, 2 bytes for the additional spaces
'abcd''abcd'4 bytes4 bytes for data
'abcdefgh'An error will occur

Oracle – Storing CHAR Strings, sql server char

Storing VARCHAR2 Strings

ValueVARCHAR2(4)Storage requiredDescription
''nullnullEmpty string is stored as null.
'ab''ab'2 bytes2 bytes for data
'abcd''abcd'4 bytes4 bytes for data
'abcdefgh'An error will occur

Oracle – Storing VARCHAR2 Strings, varchar data type

MS SQL Server

Transact SQL Reference: CHAR and VARCHAR

CHAR columnsVARCHAR columns
LengthThe length is fixed and indicates the number of bytes declared when a table is created. CHAR columns can’t store Unicode characters.

The length can be any value from 1 to 8,000 bytes.
Stores variable-length strings. The max length is specified when a table is created.

Length can be any value between 1 and 8,000 bytes.

The string’s size is the actual length of the entered data + 2 bytes.
StoringIf the ANSI_PADDING option is set to ON, then CHAR columns are padded with blank spaces.

2 bytes
When CHAR values are inserted that are longer than the specified length, the insert is aborted and an error is generated.Shorter VARCHAR values aren’t blank-padded during storage.

When VARCHAR values are inserted that are longer than the specified length, the insert is aborted and an error is generated.
4 bytes
RetrievingWhen CHAR values are retrieved, trailing spaces are removed.Spaces are retained during retrieval.

Storing CHAR Strings

ValueCHAR(4)Storage requiredDescription
'''    '4 bytes4 bytes for the blank spaces
'ab''ab '4 bytes2 bytes for data, 2 bytes for the additional spaces
'abcd''abcd'4 bytes4 bytes for data
'abcdefgh'An error will occur

MS SQL Server – Storing CHAR Strings, sql server char

Storing VARCHAR Strings

ValueVARCHAR(4)Storage requiredDescription
''''0 bytesNo characters are added when storing an empty value
'ab''ab'2 bytes2 bytes for data
'abcd''abcd'4 bytes4 bytes for data
'abcdefgh'An error will occur

MS SQL Server – Storing VARCHAR Strings, sql server char

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Learn how to process trees and graphs in SQL, and how to effectively organize long SQL queries. View course Discover our other courses: