Re: VARCHAR and indexes

jblatz@worldnet.att.net wrote in message <6vu1i2$gdg$1@nnrp1.dejanews.com>... >I recently got advice from an Informix consultant that any character >field that is 18 characters or longer should be defined as varchar. >He said that this would result in lots of saved disk space. >He made no mention of indexes and what effect varchar has on indexes. > >Can someone tell me the rule of thumb on when to use varchar and when >to use char? You need to account for the fact the VARCHAR's use one extra bte for storage. I would add the rule that the average length of the field must be expected to be at least two characters less then the max length. There is no benefit (and can be a loss) if the average length is only one less or the full field. The greater the difference between the maximum length and the average, the greater the savings. They are great for names and addresses. They allow you to make the fields nice and wide without the extra space being wasted on the disk. Disk space is cheap, but I/O bandwidth starts becoming expensive. There are notes that indexing VARCHAR's has extra overhead. That would encourage that a fields average length be even less then the above suggestion to make up for the extra costs. >We have Informix Dynamic Server ver 7.24 for Unix. We do not use 4GL >or BLOBS. > >Thanks > >-----------== Posted via Deja News, The Discussion Network ==---------- >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own S.W. wilcoxon@pioneerplanet.infi.net