varchar maximum length of 254 with an index
Benjamin Thompson asked why the documented 254-character limit for indexed VARCHARs exists, since he could index a VARCHAR(255). Art Kagel confirmed the manuals (12.10, 14.10) still state it, yet it worked on 14.10.FC8; Tom Girsch saw the same on 12.10.FC14. Benjamin has a support case on distributions that may stem from such a column.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
------------------------------
#Informix
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 ------------------------------
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 ------------------------------
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 "