Informix Guide to GLS Functionality SQL Features

This section explains how a locale affects the way that a database server handles the following SQL character data types:

• Locale-sensitive character data types: NCHAR and NVARCHAR
• Other character data types:
• CHAR
• LVARCHAR
• VARCHAR
• TEXT

For the syntax of these data types, see the Informix Guide to SQL: Syntax. For descriptions of these data types, see the Informix Guide to SQL: Reference. For information about collation order, see Character Classes of the Code Set. For information about code-set conversion, see Performing Code-Set Conversion.

The choice of locale can affect the NCHAR and NVARCHAR character data types. This section describes how this choice affects these character data types.

The NCHAR data type stores character data in a fixed-length field. This data can be a sequence of single-byte or multibyte letters, numbers, and symbols. However, the code set of your database locale must support this character data. NCHAR columns typically store names, addresses, phone numbers, and so on.

The syntax of the NCHAR data type is as follows.

Because the length of this column is fixed, when the database server retrieves or sends an NCHAR value, it transfers exactly size bytes of data. If the length of a character string is shorter than size, the database server extends the string with spaces to make up the size bytes. If the string is longer than size bytes, the database server truncates the string.

NCHAR is a locale-sensitive data type. The only difference between NCHAR and CHAR data types is the collation order. The database server collates data in NCHAR columns in localized order, if the locale defines a localized order. For most operations, the database server collates data in CHAR columns in code-set order.

Tip: The default locale (U.S. English) does not specify a localized order. Therefore, the database server sorts NCHAR data in code-set order. When you use the default locale, there is no difference between CHAR and NCHAR data. Handling NCHAR Data

Within a client application, always manipulate NCHAR data in the CLIENT_LOCALE of the client application. The client application performs code-set conversion of NCHAR data automatically if CLIENT_LOCALE differs from DB_LOCALE.

To store multibyte character data in an NCHAR column, your database locale must support a code set with these same multibyte characters. When you store multibyte characters, make sure to calculate the number of bytes that are needed. The size parameter of the NCHAR data type refers to the number of bytes of storage that is reserved for the data.

Because one multibyte character uses several bytes for storage, the value of size bytes does not indicate the number of characters that the column can hold. The total number of multibyte characters that you can store in the column is less than the total number of bytes that you can store in the column. Make sure to declare the size value of the NCHAR column in such a way that it can hold enough characters for your purposes.

If you plan to perform calculations on numbers that are stored in a column, assign a numeric data type (such as INTEGER or FLOAT) to that column. The description of the CHAR data type in the Informix Guide to SQL: Reference provides detailed reasons why you should not store certain numberic values in CHAR values. The same reasons apply for certain numeric values as NCHAR values. Treat only numbers that have leading zeros (such as postal codes) as NCHAR data types. Use NCHAR only if you need to sort the numeric values in localized order.

An NCHAR value can include tabs, spaces, and nonprintable characters. Nonprintable NCHAR and CHAR values are entered, displayed, and treated similarly.

The NVARCHAR data type stores character data in a variable-length field. This data can be a sequence of single-byte or multibyte letters, numbers, and symbols. However, the code set of your database locale must support this character data.

The syntax of the NVARCHAR data type is as follows:

Specify the reserve parameter when you initially intend to insert rows with data values having few or no characters in this column but later expect the data to be updated with longer values.

Although use of NVARCHAR economizes on space that is used in a table, it has no effect on the size of an index. In an index that is based on an NVARCHAR column, each index key has a length equal to max bytes, the maximum size of the column.

The database server does not strip an NVARCHAR object of any user-entered trailing white space, nor does it pad the NVARCHAR object to the full length of the column. However, if you specify a minimum reserved space (reserve), and some of the data values are shorter than that amount, some of the space that is reserved for rows goes unused.

The NVARCHAR data type is a locale-sensitive data type. The only difference between NVARCHAR and VARCHAR data types is the collation order. The database server collates data in NVARCHAR columns in localized order, if the locale defines a localized order. For most operations, the database server collates data in CHAR columns in code-set order.

Tip: The default locale (U.S. English) does not specify a localized order. Therefore, the database server sorts NVARCHAR data in code-set order. When you use the default locale, there is no difference between VARCHAR and NVARCHAR data. Handling NVARCHAR Data

Within a client application, always manipulate NVARCHAR data in the CLIENT_LOCALE of the client application. The client application performs code-set conversion of NVARCHAR data automatically if CLIENT_LOCALE differs from DB_LOCALE.

To store multibyte character data in an NVARCHAR column, your database locale must support a code set with these same multibyte characters. When you store multibyte characters, make sure to calculate the number of bytes that are needed. The max parameter of the NVARCHAR data type refers to the maximum number of bytes that the column can store.

Because one multibyte character uses several bytes for storage, the value of max bytes does not indicate the number of characters that the column can hold. The total number of multibyte characters that you can store in the column is less than the total number of bytes that you can store in the column. Make sure to declare the max value of the NVARCHAR column so that it can hold enough characters for your purposes.

An NVARCHAR value can include tabs, spaces, and nonprintable characters. Nonprintable NVARCHAR characters are entered, displayed, and treated in the same way as nonprintable VARCHAR characters.

Tip: The database server interprets the null character (ASCII 0) as a C null terminator. Therefore, in NVARCHAR data, the null terminator acts as a string-terminator character. Storing Numeric Values in an NVARCHAR Column

When you insert a numeric value in a NVARCHAR column, the database server does not pad the value with trailing blanks up to the maximum length of the column. The number of digits in a numeric NVARCHAR value is the number of characters that you need to store that value. For example, the database server stores a value of 1 in the mytab table when it executes the following SQL statements:

CREATE TABLE mytab (col1 NVARCHAR(10));
INSERT INTO mytab VALUES (1);

The NCHAR data type is similar to the CHAR data type, and NVARCHAR is similar to the VARCHAR data type. The difference between these data types is as follows:

• The database server collates NCHAR and NVARCHAR column values in localized order.
• The database server collates CHAR and VARCHAR column values in code-set order.

Localized collation depends on the sorting rules that the locale defines, not simply on the computer representation of the character (the code points). This difference means that the database server might perform complex processing to compare and collate NCHAR and NVARCHAR data. Therefore, access to NCHAR data might be slower with respect to comparison and collation than to access CHAR data. Similarly, access to data in an NVARCHAR column might be slower with respect to comparison and collation than access to the same data in a VARCHAR column.

Assess whether your character data needs to take advantage of localized order for collation and comparison. If code-set order is adequate, use the CHAR, LVARCHAR, and VARCHAR data types.

The choice of locale can affect the following character data types:

• CHAR
• VARCHAR
• LVARCHAR
• TEXT

This section describes how this choice affects each of these character data types.

The CHAR data type stores character data in a fixed-length field. This data can consist of letters, numbers, and symbols. The following list summarizes how choice of a locale affects the CHAR data type:

• The size of a CHAR column is byte based, not character based.

For example, if you define a CHAR column as CHAR(10), the column has a fixed length of 10 bytes, not 10 characters. If you want to store multibyte characters in a CHAR column, keep in mind that the total number of characters you can store in the column might be less than the total number of bytes you can store in the column. Make sure to define the byte size of the CHAR column so that it can hold enough characters for your purposes.

• You can enter single-byte or multibyte characters in a CHAR column.

The database locale must support the characters that you want to store in CHAR columns.

• The database server sorts CHAR columns in code-set order, not in localized order.
• Within a client application, always manipulate CHAR data in the CLIENT_LOCALE of the client application.

The client application performs code-set conversion of CHAR data automatically if CLIENT_LOCALE differs from DB_LOCALE.

The VARCHAR data type stores character data of up to 255 bytes in a variable-length field. This data can consist of letters, numbers, and symbols. CHARACTER VARYING is handled exactly the same as VARCHAR. The following list summarizes how the choice of a locale affects the VARCHAR data type:

• The maximum size and minimum reserved space for a VARCHAR column are byte based, not character based.

For example, if you define a VARCHAR column as VARCHAR(10,6), the column has a maximum length of 10 bytes and a minimum reserved space of 6 bytes. If you want to store multibyte characters in a VARCHAR column, keep in mind that the total number of characters you can store in the column might be less than the total number of bytes you can store in the column. Make sure to define the maximum byte size of the VARCHAR column so that it can hold enough characters for your purposes.

• You can enter single-byte or multibyte characters in a VARCHAR column.

The database locale must support the characters that you want to store in VARCHAR columns.

• The database server sorts VARCHAR columns in code-set order, not in localized order.
• Within a client application, always manipulate VARCHAR data in the CLIENT_LOCALE of the client application.

The client application performs code-set conversion of VARCHAR data automatically if CLIENT_LOCALE differs from DB_LOCALE.

The LVARCHAR data type stores character data greater than 255 bytes in a variable-length field. This data can consist of letters, numbers, and symbols. The database server also uses the LVARCHAR data type to represent the external format of an opaque data type.

LVARCHAR is similar to VARCHAR in the following ways:

• The LVARCHAR data type supports values greater than 256 bytes.
• The LVARCHAR data type is collated in code-set collation order.
• Client applications perform code-set conversion on LVARCHAR data.

The LVARCHAR data type supports SQL length functions similarly to the VARCHAR data type. For more information, see Using SQL Length Functions. For general information on the LVARCHAR data type, see the Informix Guide to SQL: Reference.

The TEXT data type stores any kind of text data. TEXT columns typically store memos, manual chapters, business documents, program source files, and other types of textual information. The following list summarizes how the choice of a locale affects the TEXT data type:

• The database server stores character data in a TEXT column in the code set of the database locale.
• You can enter single-byte or multibyte characters in a TEXT column.

The database locale should support the characters that you want to store in TEXT columns. However, you can put any type of character in a TEXT column.

• Text columns do not have an associated collation order.

The database server does not build indexes on TEXT columns. Therefore, it does not perform collation tasks on these columns.

• Within a client application, always manipulate TEXT data in the CLIENT_LOCALE of the client application.

The client application performs code-set conversion of TEXT data automatically if CLIENT_LOCALE differs from DB_LOCALE.