Fixed- and Varying-Length Character Data Types

The database server supports storage of fixed-length and varying-length character data. A fixed-length column requires the defined number of bytes regardless of the actual size of the data. The CHAR data type is of fixed-length. For example, a CHAR(25) column requires 25 bytes of storage for all values, so the string “This is a text string” uses 25 bytes of storage.

A varying-length column size can be the number of bytes occupied by its data. NVARCHAR, VARCHAR, and the LVARCHAR data types are varying-length character data types. For example, a VARCHAR(25) column reserves up to 25 bytes of storage for the column value, but the character string “This is a text string” uses only 21 bytes of the reserved 25 bytes. The VARCHAR data type can store up to 255 bytes of data. For information about the IFX_PAD_VARCHAR environment variable, whose setting controls how the database server sends and receives VARCHAR and NVARCHAR data values, see HCL Informix® Guide to SQL: Reference.

Because of the maximum row size limit of 32,767 bytes, a single table cannot be created with more than approximately 195 varying-length or ROW type columns.

Accessing large tables that have varying-length columns

For tables with more than a million rows, queries that use full-table scan or skip-scan access methods are more efficient if they perform light scans, rather than bufferpool scans. Light scans are not supported, however, on tables that include NVARCHAR, VARCHAR, or LVARCHAR data types columns, or columns of DISTINCT data types whose base types are a varying-length column, unless the BATCHEDREAD_TABLE configuration parameter (or the BATCHEDREAD_TABLE session environment option) is set to 1.

Restriction:

This dependency of light scans on BATCHEDREAD_TABLE being enabled also applies to tables whose schema or storage attributes include any of the following:

table compression
columns of any variable-length data type
rows that occupy more than a single page of storage.

For more information about when the query optimizer can choose execution paths that perform light scans to access large tables, see your HCL Informix Performance Guide.