View Only
  • 1.  varchar maximum length of 254 with an index

    Posted 21 days ago

    I learnt very early on in my Informix career that the maximum length of a varchar column is limited to 254 characters, instead of 255, if the column is indexed. This restriction is documented here:

    Today I have come across a column which is varchar(255) and used in multiple indices.

    Could anyone shed any light on any of the following aspects:
    * why this restriction exists (or existed).
    * whether it still exists.
    * what bad things might happen if it is violated.
    * why the db engine does not enforce it.

    This little dialogue shows that, on the face of it, it is not a problem:

    drop table if exists t1;

    Table dropped.

    create table t1 (

      col1 varchar(255)


    Table created.

    insert into t1 (col1) values ('012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234');

    1 row(s) inserted.

    select col1, len(col1) from t1;

    col1          012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789


    (expression)  255

    1 row(s) retrieved.

    create unique index ix_t1 on t1(col1);

    Index created.

    select * from t1 where col1='012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234';

    col1  01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567


    1 row(s) retrieved.

    Benjamin Thompson

  • 2.  RE: varchar maximum length of 254 with an index

    IBM Champion
    Posted 21 days ago

    As of the v12.10 and 14.10FC6 documentation the restriction still officially exists. From the Guide to SQL Syntax (v12.10.xC8 - V14.10 manual is the same):

    VARCHAR and NVARCHAR: 1 ≤ max ≤ 255 (or 1 ≤ max ≤ 254, if indexed)

    And I did test your code on 14.10.FC8 and agree, you can index a VARCHAR(255).


    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.

  • 3.  RE: varchar maximum length of 254 with an index

    Posted 20 days ago
    Thanks Art, the reason for asking is a case we have open with support at the moment about building distributions on such a column. There is no conclusion yet but it does look like use of an indexed varchar(255) may be at the root of it. So maybe the manual is right.

    Benjamin Thompson

  • 4.  RE: varchar maximum length of 254 with an index

    IBM Champion
    Posted 20 days ago
    I just tested, and it also appears to be possible to index a VARCHAR(255) with 12.10.FC14

    Tom Girsch

    Lead System Architect
    Auto Europe Group

    " If you think there is something more important than a Client ... think again "