Re: char or varchar for small colums?

Rainer Schaub wrote: > > I'm only thinking to take varchar if 1.) the table have or will have > many rows (e.g. over 100.000) and 2.) the charcterfield has more than 10 > Bytes. > Very important is the fact that the field shoud'nt be changed anymore. > for example you insert "i don't like mondays" in a varchar(50,1) and > after some day you change it to "i dont't like working on mondys" than > this row will probably spread over two pages. and this is really bad. This is not exactly right. Informix never spreads a row over two pages unless the row itself is larger than 2020 bytes. However, if there is not enough free space on the page where the row is currently (the engine will compress all of the free space on the page first to see if it can fit the newly expanded row on the same page) then the row will be moved to another page where there is enough space and a forwarding pointer will be left on the original page (the slot pointer contains the new rowid instead of an in-page offset) so that the rowid of the row stays the same. If this happens then two I/Os are needed to fetch that row. RULE OF THUMB: If you have a column that may contain a few characters in most rows but many more characters in other rows then use varchar to save disk space. For small columns and any relatively fixed size strings use char. > you have too know that varchar saves diskspace (sometimes a lot) but it > cost a little cpu. the saving of diskspace means that much more rows fit > in a page and therefore you speed up data-read. and disks are much more > slowly than cpu!!! True! > > I am looking for some hints on char vs varchars. > > Is there any overhead for varchar? I have a many small colums (i.e. 1 > > to 5 characters) and wonder if I am better of using char or varchar. > > So far I have been using char for <=3 and varchar for colums >= 4 > > characters. Art S. Kagel