CHAR and VARCHAR
<< URN (Uniform Resource Name) | IBExpert Glossary | BINARY, VARBINARY, BINARY VARYING >>
CHAR
and VARCHAR
Firebird/InterBase® provides two basic data types to store text or character information: CHAR
and VARCHAR
(blobs also allow character storage using the subtype text).
CHAR
and VARCHAR
are data types which can store any text information. Numbers that are not calculated, such as zip codes, are traditionally stored in CHAR
or VARCHAR
columns. The length is defined as a parameter, and can be between 1 and 32,767 bytes. It is particularly useful for codes that typically have a fixed or predefined length, such a the zip code for a single country.
Compared to most other databases, Firebird/InterBase® only stores significant data. If a column is defined as CHAR(100)
, but only contains entries with 10 characters, the additionally defined bytes are not used, as Firebird/InterBase® stores CHAR
and VARCHAR
types similarly, and does not fill unused spaces with blanks. Both CHAR
and VARCHAR
are stored in memory buffer in their full, declared length; but the whole row is compressed prior to storing i.e. CHAR
s, VARCHARS
s, INTEGER
s, DATES
s, etc. all together.
Indeed, VARCHAR
columns require more storage than CHAR
columns, because when storing a VARCHAR
, Firebird/InterBase® adds two bytes that state just how big the VARCHAR
actually is.
So a CHAR
will in fact be stored in a smaller space. However, when a SELECT
is performed on a VARCHAR
column, Firebird/InterBase® strips the 2 byte padding and returns the stored value. When a SELECT
is performed on a CHAR
column, Firebird/InterBase® returns the value and the "empty spaces". Thus the two bytes saved in storage of a CHAR
must be balanced against the subsequent need to strip the spaces on the client side. These two bytes however are, with today's hardware, too negligible to have an influence upon the database performance. This can however be disadvantageous when defining short text fields.
In practical terms consider just this one rule: only use CHAR
s if strings of few characters are to be stored; the exception to the rule being when working with intermediate tables that are required to export data to fixed length prn
files. Then the fixed length field will be a positive advantage.
This efficient storage in Firebird/InterBase® can lead to considerable confusion particularly when importing data, as Paradox or dBase databases save all blank spaces, and after importing a 10MB dBase file into InterBase®, often only 3-6 MB remain, although all data sets were imported correctly.
For this reason columns can be defined generously in Firebird/InterBase® without a problem, whereas in other databases each defined byte influences the size of the database, regardless of whether data is stored in these fields or not.
Please note however that indexed CHAR
fields should not be more than approx. 80 characters in length (with Firebird 1.5 the limit is somewhat higher).
Another factor to take into consideration when defining the length of CHAR
and VARCHAR
fields is the size of temporary files, as these also include the full space definition for long CHAR
or VARCHAR
columns. Many developers are very generous when defining CHAR
s and VARCHAR
s because they know that Firebird stores them in binary form with the string content plus the number of empty spaces (and not the empty spaces themselves). However, when these fields are loaded into the temp directory, they are written out in full. For this reason it often makes more sense to define a text BLOB
rather than a huge VARCHAR
. BLOB
fields are only limited in size by the page size; for example, with a page size of 8 KB the maximum blob size is 32 Gigabytes.
The CHAR
data type definition can be written in two ways:
CHAR CHARACTER
The VARCHAR
data type definition can be written as follows:
VARCHAR CHARACTER VARYING CHAR VARYING
See also:
The Firebird server and VARCHAR
s
Selecting the right data type to improve database performance
Firebird 2.5 Language Reference Update
Firebird 2.1 Language Reference Update
Firebird 2.0 Language Reference Update
SQL Language Reference
Table Editor
SQL Editor
Data types and subtypes
NCHAR
and NVARCHAR
back to top of page
<< URN (Uniform Resource Name) | IBExpert Glossary | BINARY, VARBINARY, BINARY VARYING >>