Using Character Data Types
The choice of a locale can affect the way that the database server handles character data. The section explains how this choice affects data in the following SQL character data types:
You specify data types in data definition statements such as CREATE TABLE and ALTER TABLE.
The NCHAR Data Type
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:
In this format, the size parameter specifies the number of bytes in the column. The total length of an NCHAR column cannot exceed 32,767 bytes for Universal Server and OnLine Dynamic Server or 32,511 bytes for SE. If you do not specify size, the database server assumes NCHAR(1). For the complete syntax of the NCHAR data type, see the Data Type section in the Informix Guide to SQL: Syntax.
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.
Collating NCHAR Data
The NCHAR data type 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. The database server collates data in CHAR columns in code-set order. For more information on collation order, see "The Collation Order".
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. For information on code-set conversion, see page 1-40.
Multibyte Characters with NCHAR
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 might use 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.
Treating NCHAR Values as Numeric Values
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 gives detailed reasons why you should not treat certain numbers as 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.
Nonprintable Characters with NCHAR
An NCHAR value can include tabs, spaces, and nonprintable characters. Nonprintable NCHAR and CHAR values are entered, displayed, and treated similarly. For more information on nonprintable characters, see the description of the CHAR data type in the Informix Guide to SQL: Reference.
The NVARCHAR Data Type
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:
In this format, the max parameter specifies the maximum number of bytes that can be stored in the column, and the reserve parameter specifies the minimum number of bytes that are reserved for the column. You must specify the maximum size (max) of the NVARCHAR column. The size of this parameter cannot exceed 255 bytes. When you place an index on an NVARCHAR column, the maximum size is 254 bytes. You can store shorter, but not longer, character strings than the value that you specify.
You can optionally specify the minimum reserved space (reserve) parameter. This value can range from 0 to 255 bytes but must be less than the maximum size (max) of the NVARCHAR column. If you do not specify a minimum space value, the default value of reserve is 0. Specify the reserve parameter when you initially intend to insert rows with short or null data 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 spaces, 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.
For the complete syntax of the NVARCHAR data type, see the Data Type segment in the Informix Guide to SQL: Syntax.
Collating NVARCHAR Data
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. The database server collates data in VARCHAR columns in code-set order. For more information on collation order, see "The Collation Order".
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. For information on code-set conversion, see page 1-40.
Multibyte Characters with NVARCHAR
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 might use 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.
Nonprintable Characters with NVARCHAR
An NVARCHAR value can include tables, spaces, and nonprintable characters. Nonprintable NVARCHAR characters are entered, displayed, and treated in the same way as nonprintable VARCHAR characters. For detailed information on how to enter and display nonprintable characters, see the description of the VARCHAR data type in the Informix Guide to SQL: Reference.
Storing Numeric Values in an NVARCHAR Column
When you insert a numeric value into 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:
Performance Considerations for NCHAR and NVARCHAR
The NCHAR data type is very similar to the CHAR data type, and NVARCHAR is very similar to the VARCHAR data type. The difference between these data types is as follows:
Localized collation is dependent 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 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 and the VARCHAR data types.
Other Character Data Types
The choice of locale can affect the character data types CHAR, VARCHAR and TEXT. This section describes how this choice affects each of these character data types.
The CHAR Data Type
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:
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.
The database locale must support the characters that you want to store in CHAR columns.
The client application performs code-set conversion of CHAR data automatically if CLIENT_LOCALE differs from DB_LOCALE. For information on code-set conversion, see page 1-40.
For the syntax of the CHAR data type, see the Informix Guide to SQL: Syntax. For a detailed explanation of the CHAR data type, see the Informix Guide to SQL: Reference.
The VARCHAR Data Type
The VARCHAR data type stores character data in a variable-length field. This data can consist of letters, numbers, and symbols. The following list summarizes how the choice of a locale affects the VARCHAR data type:
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.
The database locale must support the characters that you want to store in VARCHAR columns.
The client application performs code-set conversion of VARCHAR data automatically if CLIENT_LOCALE differs from DB_LOCALE. For information on code-set conversion, see page 1-40.
For the syntax of the VARCHAR data type, see the Informix Guide to SQL: Syntax. For a detailed explanation of the VARCHAR data type, see the Informix Guide to SQL: Reference.
The TEXT Data Type
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 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.
The database server does not build indexes on TEXT columns. Therefore, it does not perform collation tasks on these columns. For more information on collation order, see "The Collation Order".
The client application performs code-set conversion of TEXT data automatically if CLIENT_LOCALE differs from DB_LOCALE. For information on code-set conversion, see page 1-40.
For the complete syntax of the TEXT data type, see the Informix Guide to SQL: Syntax. For a detailed explanation of the TEXT data type, see Chapter 3 of the Informix Guide to SQL: Reference.
|
