Variable-length strings: CHARACTER VARYING(m,r), VARCHAR(m,r), NVARCHAR(m,r), and LVARCHAR(m)

Often the data values in a character column are different lengths. That is, many are an average length, and only a few are the maximum length. For the following data types, m represents the maximum number of bytes and r represents a minimum number of bytes that the column stores. These variable-length data types are designed to save disk space when you store such data:

CHARACTER VARYING (m,r): The CHARACTER VARYING (m,r) data type contains a sequence of, at most, m bytes or at the least, r bytes. This data type is the ANSI-compliant format for character data of varying length. CHARACTER VARYING (m,r), supports code-set order for comparisons of its character data.
VARCHAR (m,r): The VARCHAR (m,r) is one of the HCL® OneDB®-specific data types for storing character data of varying length. In functionality, it is the same as CHARACTER VARYING(m,r).
NVARCHAR (m,r): The NVARCHAR (m,r) is one of theHCL OneDB-specific data types for storing character data of varying length. It compares character data in the order that the locale specifies.
LVARCHAR(m): The LVARCHAR is the HCL OneDB-specific data type for storing character data of varying length from 1 to 32,739 bytes. If no maximum size is specified in the declaration of a column length, the default is 2,048 bytes. LVARCHAR supports code-set order for collation, and is also used by the database server for internal operations on character strings, whose maximum size is operating-system dependent.

Tip: The difference in the way data is compared distinguishes NVARCHAR(m,r) data from CHARACTER VARYING(m,r) or VARCHAR(m,r) data. For more information about how the locale determines code-set and sort order, see Character data: CHAR(n) and NCHAR(n).

In databases that are created as NLSCASE INSENSITIVE, only the NCHAR and NVARCHAR data types are processed by the database server without regard to letter-case variants, so that (for example) the NCHAR strings ’pH’ and ’Ph’ are treated as duplicate values in ordering, sorting, and comparison operations.

When you define columns as variable-length data types, you can specify m as the maximum number of bytes. If an inserted value consists of fewer than m bytes, the database server does not extend the value with single-byte spaces (as with CHAR(n) and NCHAR(n) values). Instead, it stores only the actual contents on disk with a 1-byte length field. The limit on m is 254 bytes for indexed columns and 255 bytes for non-indexed columns.

The second parameter, r, is an optional reserve length that sets a lower limit on the number of bytes than a value being stored on disk requires. Even if a value requires fewer than r bytes, r bytes are nevertheless allocated to hold it. The purpose is to save time when rows are updated. (See Variable-length execution time.) The LVARCHAR data type supports no reserve length.

The advantages of the CHARACTER VARYING(m,r), LVARCHAR(m), or VARCHAR(m,r) data type over the CHAR(n) data type are as follows:

They conserve disk space when the number of bytes that data items require varies widely, or when only a few items require more bytes than average.
Queries on the more compact tables can be faster.

These advantages also apply to the NVARCHAR(m,r) data type in comparison to the NCHAR(n) data type.

The following are potential disadvantages of using varying-length data types:

Except for LVARCHAR, they do not support lengths that exceed 255 bytes.
Table updates can be slower in some circumstances.