Informix

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

    Posted Tue July 26, 2022 10:43 AM
    All,

    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:
    https://www.ibm.com/docs/en/informix-servers/14.10?topic=types-varcharmr-data-type

    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

                  012345678901234567890123456789012345678901234567890123456789012345678901234

    (expression)  255

    1 row(s) retrieved.

    create unique index ix_t1 on t1(col1);

    Index created.

    select * from t1 where col1='012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234';

    col1  01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567

          8901234567890123456789012345678901234567890123456789012345678901234

    1 row(s) retrieved.



    ------------------------------
    Benjamin Thompson
    ------------------------------


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

    IBM Champion
    Posted Tue July 26, 2022 03:47 PM
    Ben:

    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

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



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

    Posted Wed July 27, 2022 08:47 AM
    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 Wed July 27, 2022 09:01 AM
    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
    tgirsch@autoeurope.com


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