Storing Character Data - Teradata Database

Storing Character Data

For character data, an alternative to encodings and value compressing fixed‑length CHARACTER(n) strings is to specify the variable‑ length VARCHAR or LONG VARCHAR data types. The number of bytes used to store each VARCHAR or LONG VARCHAR column is the length of the data item plus 2 bytes. Contrast this to the fixed‑length CHARACTER data type which uses n bytes per row, regardless of the actual number of characters in each individual column.

The demographics of the data determine whether VARCHAR, LONG VARCHAR, or CHARACTER plus multivalue compression is more efficient. The most important factors are:

Maximum column length

Average column length

Evaluate the following factors when determining which approach to storing the data is the more efficient:

VARCHAR or LONG VARCHAR are more efficient when the difference of maximum and average column length is high and value compressibility is low.

Multivalue compression with CHARACTER data is more efficient when the difference of maximum and average column length is low and value compressibility is high.

When neither CHARACTER nor VARCHAR/LONG VARCHAR is clearly a superior choice, use VARCHAR or LONG VARCHAR because their data requires slightly less CPU resource to manipulate than CHARACTER data.